VBA Excel Hide Blank Rows Automatically

VBA Excel Hide Blank Rows Automatically

This is a question originally posted in Microsoft Community about Excel hide blank rows.

https://answers.microsoft.com/en-us/office/forum/office_2007-excel/automatically-unhide-a-row-on-sheet-2-when-data-is/b1c85ae4-d0a8-4339-86c4-4ee68471ff73

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.

Leave a Reply

Your email address will not be published.