Macro to hide Pivot Table Rows where Col E has zero values

HowardC · May 29th 2017

I have attempted to write code to hide rows on a Pivot table where Col E has zero values from row 4 onwards

Code

Sub Hide_Zeroes()
Dim LR As Long
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    For I = LR To 4 Step -1
    If Cells(I, 5 & LR).Value = 0 Then Cells(I, 5).EntireRow.Hidden = True
    Next
    
    
    End Sub

I need someone to kindly assist me

**Ger Plante** · May 31st 2017

Re: Macro to hide Pivot Table Rows where Col E has zero values

any reason why you added 5+LR? .... 5 is the column and LR is the last row, so it doesnt make sense to add them. try this?

Code

Sub Hide_Zeroes() 
    Dim LR As Long 
    LR = Cells(Rows.Count, "A").End(xlUp).Row 
    For I = LR To 4 Step -1 
        If Cells(I, 5).Value = 0 Then Cells(I, 5).EntireRow.Hidden = True 
    Next 
     
     
End Sub

alternatively, try this approach with no loops.

Code

Sub Hide_Zeroes()
    
    Range("E4:E" & Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter 1, "<>0"
     
End Sub

HowardC · May 31st 2017

Re: Macro to hide Pivot Table Rows where Col E has zero values

Thanks for the help

Macro to hide Pivot Table Rows where Col E has zero values

Participate now!

Share