VBA example - Excel 2007: hide rows in Excel sheet

VBA code to hide rows in Excel sheet

May people on the web have asked the same questions (How to hide rows in Excel?) may times. The way to hide the rows in this example is using the SendKeys method to send the shortcut key to the Excel application. The shortcut key to hide the rows in Excel is the combination of Ctrl and 9 keys. Therefore, to hide the selected rows the VBA code should be SendKeys "^9".

To run this example VBA code, you will need to copy and paste the example VBA code in the Activate procedure block of your worksheet. To open the Activate block, double-click the worksheet then in the objects dropdown list select Worksheet and in the Events dropdown list select Activate.

VBA code for this example:

Private Sub Worksheet_Activate()  
'Combine the odd rows among 50 rows
'The result string st stores multiple odd rows
'in the form of 1:1,3:3,5:5,7:7,...
Dim rs As String
rs = ""
Dim i As Integer
i = 1
While i <= 50
rs = rs & i & ":" & i & ","
i = i + 2
rs = Left(rs, Len(rs) - 1) 'Remove one comma at the end
ActiveSheet.Range(rs).Select 'Select the range
'Hide the selected rows by sending the key: Ctrl+9
   SendKeys "^9"
End Sub


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.