Excel VBA Hiding Rows

Question

In the picture below, I'm trying to hide the rows that are empty in a certain cells (e.g. Row 39 to Row 48). Is is possible to do it in one click? I'm planning of doing it with VBA.

This is the formula that I'm currently using but the thing is the cells that I want to hide may not start at row 39 or ends at row 48, it depends on the data.

Sub HideRows()
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("NAMES", "AUGUST"))
        'ws.Rows("39:48").Hidden = True
Next
End Sub

What are the limits of the range where you want to hide empty cells? — Tim Williams
– Tim Williams, Commented Dec 13, 2021 at 6:33

VBasic2008 · Accepted Answer · 2021-12-13 11:38:22Z

1

Hide 'Empty' Rows

This is a slightly different approach:
- uses Option Explicit
- uses constants and variables
- uses For Each...Next loops for both, worksheets and cells
- qualifies all objects (e.g. ws.Cells or rg.Cells, not just Cells)
- combines empty cells into a range
- unhides all rows in one go, then hides the 'empty' rows in another (go)

Option Explicit

Sub HideRows()

    Const StartRow As Long = 9
    Const EndRow As Long = 89
    Const ColNum As Long = 3
    Dim WorksheetNames As Variant
    WorksheetNames = Array("NAMES", "AUGUST") ' add more
    
    Dim ws As Worksheet ' Current Worksheet
    Dim rg As Range ' Current Range
    Dim hrg As Range ' Current Hide Range
    Dim cCell As Range ' Current Cell in Range
    
    ' Loop through the worksheets in the workbook containing this code.
    For Each ws In ThisWorkbook.Worksheets(WorksheetNames)
        ' Create a reference to the range of the current worksheet.
        Set rg = ws.Range(ws.Cells(StartRow, ColNum), ws.Cells(EndRow, ColNum))
        ' or using resize:
        'Set rg = ws.Cells(StartRow, ColNum).Resize(EndRow - StartRow + 1)
        ' Loop through the cells of the current range.
        For Each cCell In rg.Cells
            If IsEmpty(cCell) Then ' cell is empty
                ' Combine ('add') the current cell into the hide range.
                If Not hrg Is Nothing Then ' for all except the first
                    Set hrg = Union(hrg, cCell)
                Else ' for the first
                    Set hrg = cCell
                End If
            'Else ' cell is not empty - do nothing
            End If
        Next cCell
        ' Unhide all rows of the current range of the current worksheet.
        rg.EntireRow.Hidden = False
        If Not hrg Is Nothing Then ' there are combined cells
            ' Hide the rows of the hide range.
            hrg.EntireRow.Hidden = True
            ' Reset the hide range variable for the next worksheet.
            ' Also, note that 'Union' works only with ranges from one worksheet.
            Set hrg = Nothing
        'Else ' there are no combined cells - do nothing
        End If
    Next ws

End Sub

answered Dec 13, 2021 at 11:38

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

cjwrk Over a year ago

I tried using your code above however it only works in the NAMES sheet and not with the other sheets.

VBasic2008 Over a year ago

Still working correctly on my end. Which other worksheets? You need to add their names to the array. The code has only Names and August. If you add a non-existing name, the code will fail.

cjwrk Over a year ago

I delete the other worksheets and only have the NAMES and AUGUST sheets but it only works on my NAMES sheet and not with the AUGUST sheet.

VBasic2008 Over a year ago

Is sheet AUGUST protected? Do you have merged cells? Is your data filtered? It works when the sheet is hidden.

cjwrk Over a year ago

I figured it out. There's no error with your code. I think the problem is because my AUGUST sheet has a formula =IF(NAMES!C75="","",NAMES!C75) which copy the data from the NAMES data.

|

cjwrk · Accepted Answer · 2021-12-13 06:41:48Z

1

I already made it. Below is the script that I used.

Sub HideRows()

    Dim ws As Worksheet
    For Each ws In Worksheets(Array("NAMES", "AUGUST"))
    
        StartRow = 9
        EndRow = 89
        ColNum = 3
    
        For i = StartRow To EndRow
            If Not IsEmpty(Cells(i, ColNum).Value) Then
                ws.Cells(i, ColNum).EntireRow.Hidden = False
            Else
                ws.Cells(i, ColNum).EntireRow.Hidden = True
            End If
        Next i
    
    Next
End Sub

answered Dec 13, 2021 at 6:41

cjwrk

3156 silver badges18 bronze badges

Collectives™ on Stack Overflow

Excel VBA Hiding Rows

2 Answers 2

Hide 'Empty' Rows

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Hide 'Empty' Rows

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related