Excel VBA Hiding Rows-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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