VBA Excel Hide Blank Rows Automatically
This is a question originally posted in Microsoft Community about Excel hide blank rows.
The user has a worksheet called Sheet1 that is dynamically changed. Another worksheet, Sheet2, contains a range of formula that references to Sheet1. If the corresponding Sheet1 data is blank, Sheet2 Cell shows nothing.
Simply speaking, the user wants to hide any blank rows and unhide non-blank rows automatically.
Solution to Excel Hide Blank Rows
To do this, I need to think about how to automate the Macro without manual trigger. Worksheet Activate Event should be a good choice, because every time the user activates a worksheet, the hide and unhide Macro gets to trigger. Then I want user to provide the parameters for start column number, end column number, start row number and end rownumber, because I want to loop through every cell in the Range to determine the definition of blank row, otherwise I need extra code to define the last row and last column myself, which I think is not accurate enough.
Below is the solution I provided:
Below is an example, you can define stCol (start column number), endCol (end column number),stRow, endRow. My example demonstrate how to hide blank rows and unhide non-blank rows for data range A1:J20
Private Sub Worksheet_Activate() stCol = 1 endCol = 10 stRow = 1 endRow = 20 For r = stRow To endRow counter = 0 For c = stCol To endCol If Cells(r, c).Value = "" Then counter = counter + 1 End If Next c If counter = endCol Then Rows(r).EntireRow.Hidden = True Else Rows(r).EntireRow.Hidden = False End If Next r End Sub
The above code contains nested loops. The first For Loop loops through each Cell within the same row, and count if there is any value in each Cell. If it contains something, add 1 to counter. After looping through one row, if counter equals to endRow number (20 in this case), it means the whole row is empty, then I can hide the row, otherwise unhide the row.
After looping through all Cells in the same row, loop another row until the end of the row. Reset counter for each row.