Hiding Rows Based On Different Column Data Ranges

Question

I am fairly new to using VBA and am trying to create a code that will look at two different columns with varying data ranges and hide rows beyond the last data point (referencing both columns).

At the moment I have this;

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Updateby Extendoffice 20160913
    Dim xRg As Range
    Application.ScreenUpdating = False
        For Each xRg In Range("G24:G71, N24:N71")
            If xRg.Value = "" Then
                xRg.EntireRow.Hidden = True

            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.ScreenUpdating = True
End Sub

Column G and Column N are peoples names in two separate pivot tables. So depending on the day the range of data in each of these columns can differ (the pivot table has different filters). For example today there could be 50 rows of data in Column G and 40 in Column N. In this case the above formula would work and hide rows 51 to 71 with no data in. However, if Column G has 40 rows of data and Column N has 50 rows then it would reference column G and hide rows 41 - 71, hiding unwanted data from column N.

Is there a way to get the code to look at Columns G & N, identify which has a larger data range and hide rows beyond that point.

Thanks in advance for any help.

Answer 1

Try this one:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim xRg As Range, xCell As Range, bHide As Boolean
    Application.ScreenUpdating = False
        For Each xRg In Range("G24:G71, N24:N71").Rows
            bHide = True
            For Each xCell In xRg.Cells
                If IsEmpty(xRg.Value) = False Then
                    bHide = False
                End If
            Next xCell
            xRg.EntireRow.Hidden = bHide
        Next xRg
    Application.ScreenUpdating = True
End Sub

Explanation of the error: your code iterates through all cells, and makes a decision based on individual cells. That is incorrect because you want to know if both cells in a row are empty.

Solution: So you should iterate rows in an outer loop, and cells inside the given row in an inner loop. Take note if there is any cell that is non-empty, and make the decision on hiding the row based on this.

Update

Sorry, my code did not work because Range("G24:G71, N24:N71") consists of 2 .Areas, and although .Rows.Count returns 48, For Each enumerates 96 "rows", each consisting of 1 cell (48 rows for each area).

I modified the code to take into account Areas:

Private Sub Worksheet_PivotTableUpdate()
    Application.ScreenUpdating = False
    With Range("G24:G71,N24:N71")
        Dim r As Long: For r = 1 To .Areas(1).Rows.Count
            Dim bHide As Boolean: bHide = True
            Dim xArea As Range: For Each xArea In .Areas
                If IsEmpty(xArea.Cells(r, 1).Value) = False Then
                    bHide = False
                End If
            Next xArea
            .Rows(r).EntireRow.Hidden = bHide
        Next r
    End With
    Application.ScreenUpdating = True
End Sub