Pivot filters to hide certain rows

**Hanskubansku** · 10-30-2012, 11:12 AM

Hi all!

I'm having some trouble figuring out how to use the filters to get the view I want.. I think the solution is simple, however couldn't figure it out yet....
Attached is an example. In my original file I have multiple companies and multiple customers. What I would like to do is hide the customer rows that have no data in them - however to still display other customer data for that company. If I use filter, it will filter all the companies too, which have no certain customer data. What options should I choose? I did a macro that hides all empty rows, but i'm pretty sure the pivot table can handle this kind of task... I just don't know about it. Thanks for your help!!

**JieJenn** · 10-30-2012, 11:32 AM

Probably have to go with VBA

Sub HideRows()
Dim LR As Long, i As Long

With Worksheets("Pivot")
LR = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 5 To LR
        If Not UCase(Left(.Cells(i, 1), 6)) = "COMPAN" And WorksheetFunction.Count(.Cells(i, 2).Resize(1, 3)) = 0 Then
            .Rows(i).Hidden = True
        End If
    Next i
End With
End Sub