How to hide row if all visible cells in cell range are blank in VBA for Excel?

Question

I am running some VBA macros in my Excel spreadsheet to help me eliminate irrelevant data from view to then allow me to export/copy a subset of the overall set of data I have. My spreadsheet size is Columns(A:BN) and Rows(1:4693), which I would imagine is a decent size data set. Columns(A:G) will always remain visible. I'm only interested in hiding what is in Columns(H:BN).

First, If I want to keep the column in view, I set the column value to "Y", then I click the 'Hide Columns" button. I do this by attaching this subroutine to a button:

Sub Hidecolumn()
Dim p As Range
    For Each p In Range("H1:BN1").Cells
        If p.Value = "N" Then
            p.EntireColumn.Hidden = True          
        End If
    Next p  
End Sub

This works perfectly.

I also have a "Show All Columns" button to have all of the hidden columns reappear, and I do this with:

Sub Unhidecolumn()
Dim p As Range
    For Each p In Range("H1:BN1").Cells
        If p.Value = "Y" Or p.Value = "N" Then
            p.EntireColumn.Hidden = False    
        End If
    Next p   
End Sub

Now that I have the columns I want, now I need to hide all of the unwanted rows. Each cell will have a "Y" in it if that row-column relationship exist. It will be blank if there is no relationship. I want to hide all rows where all of the visible cells in the cell range are blank. ALL CELLS in the cell range must be blank.

Once I've hidden all of the unwanted rows, then I can copy the remaining/visible data and copy it to a new workbook to share with others, because they only need the data relevant to them.

Finally, just like I am able to unhide all of the hidden columns, I need to also be able to unhide all of the hidden rows.

In the end, I want 4 buttons on my sheet.
1. Hide Columns 2. Unhide Columns 3. Hide Rows 4. Unhide Rows

I already have the first 2, now I need the last 2. Please help.

UPDATE: SOLUTION - Thank you @K.Davis!

Sub HideRowsSecond()

    Dim srcRng As Range, ws As Worksheet
    Set ws = ActiveSheet
    Set srcRng = ws.Rows("5:" & ws.Cells(ws.Rows.Count, 4).End(xlUp).Row)

    Dim R As Range, hideRng As Range
    For Each R In srcRng
        If Application.CountA(R.Columns("H:BN").SpecialCells(xlCellTypeVisible)) = 0 Then
            If hideRng Is Nothing Then
                Set hideRng = R.EntireRow
            Else
                Set hideRng = Application.Union(hideRng, R.EntireRow)
            End If
        End If
    Next R

    If Not hideRng Is Nothing Then hideRng.EntireRow.Hidden = True
    MsgBox ("Complete")
End Sub

K.Dᴀᴠɪs K.Dᴀᴠɪs · Accepted Answer · 2019-04-30T23:22:08

You can use the pre-built CountA() worksheet function to count the number of cells that have data in them. This count will return a value of 0 if the entire row is blank.

I would use UsedRange so you are not unnecessarily counting past your data. I would also union ranges to be hidden and hide them all at once, instead of one at a time for better performance.

Sub HideRows()

    Dim r As Range, hideRng As Range

    For Each r In ActiveSheet.UsedRange.Rows
        If Application.CountA(r.EntireRow) = 0 Then
            If hideRng Is Nothing Then
                Set hideRng = r.EntireRow
            Else
                Set hideRng = Application.Union(hideRng, r.EntireRow)
            End If
        End If
    Next r

    If not hideRng is nothing then hideRng.EntireRow.Hidden = True

End Sub

You don't need to overcomplicate the unhiding of the rows. This will unhide your rows without looping.

Sub UnHideRows()

    ActiveSheet.Cells.EntireRow.Hidden = False

End Sub

Thanks to Chris Neilsen pointing out that you may have only wanted to check if the visible columns had any data, you can use this in place of the first code:

Sub HideRows()

    Dim r As Range, hideRng As Range

    UnHideRows

    For Each r In ActiveSheet.UsedRange.Rows
        If Application.CountA(r.EntireRow.SpecialCells(xlCellTypeVisible)) = 0 Then
            If hideRng Is Nothing Then
                Set hideRng = r.EntireRow
            Else
                Set hideRng = Application.Union(hideRng, r.EntireRow)
            End If
        End If
    Next r

    If Not hideRng Is Nothing Then hideRng.EntireRow.Hidden = True

End Sub

Per OP's response of only wanting to look in the visible rows between the columns of H:BN, you can use the following to accomplish this:

Sub HideRows()

    Dim srcRng As Range, ws As Worksheet
    Set ws = ActiveSheet
    Set srcRng = ws.Rows("1:" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)

    UnHideRows

    Dim r As Range, hideRng As Range
    For Each r In srcRng
        If Application.CountA(r.Columns("H:BN").SpecialCells(xlCellTypeVisible)) = 0 Then
            If hideRng Is Nothing Then
                Set hideRng = r.EntireRow
            Else
                Set hideRng = Application.Union(hideRng, r.EntireRow)
            End If
        End If
    Next r

    If Not hideRng Is Nothing Then hideRng.EntireRow.Hidden = True

End Sub

In this function, I went ahead and created a new Range variable srcRng, because using the Columns() function with UsedRange can sometimes cause issues. Since we are no longer using UsedRange, we are now finding the last use row with ws.Cells(ws.Rows.Count, 1).End(xlUp).Row.

How to hide row if all visible cells in cell range are blank in VBA for Excel?

1 Answers