Ggtcf

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.

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

This is another way to loop pivot table. The below code loop in two different columns (Items & Quantity) and hidden rows:

Option Explicit

Sub Hide()

 Dim pvtTable As PivotTable
 Dim pvtItem As PivotItem
 Dim pvtRow As Long

 'Set table with name
 Set pvtTable = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1")
 'Loop Items in a specific field
 For Each pvtItem In pvtTable.PivotFields("Item").PivotItems
 'Check conditions
 pvtRow = pvtRow + 1

 If pvtItem.Value = "A" Then
 pvtTable.DataBodyRange.Rows(pvtRow).EntireRow.Hidden = True
 ElseIf pvtItem.Value <> "A" And pvtTable.DataBodyRange.Rows(pvtRow).EntireRow.Hidden = True Then
 pvtTable.DataBodyRange.Rows(pvtRow).EntireRow.Hidden = False
 End If

 Next pvtItem

 'Loop Items in a specific field
 For Each pvtItem In pvtTable.PivotFields("Quantity").PivotItems
 'Check conditions
 pvtRow = pvtRow + 1

 If pvtItem.Value = "A" Then
 pvtTable.DataBodyRange.Rows(pvtRow).EntireRow.Hidden = True
 ElseIf pvtItem.Value <> "A" And pvtTable.DataBodyRange.Rows(pvtRow).EntireRow.Hidden = True Then
 pvtTable.DataBodyRange.Rows(pvtRow).EntireRow.Hidden = False
 End If

 Next pvtItem

End Sub