Autofilter based on Dynamic Cell Values (Pivot Table)

[quixter]

Hi all,

I have a pivot table that has adjacent columns that work independently of the pivot table. I linked a drop down that users can select from to to control how the pivot table is filtered.

I want to Autofilter out the rows that are blank after the user selects from the drop down, but it seems like the rows that are currently hidden are based on the user's previous choice via the drop down.

sumws.Range("K13:M42").AutoFilter Field:=1, Criteria1:="<>0", Visibledropdown:=False

Do I need to declare a variable that will load which rows are blank after my user selects from the drop down, then use the auto filter? If so, what is the best way to go about doing that?

Thanks.

 

[quixter]

As opposed to using auto filter, I looked on the internet for a For Loop instead. It seems to work better, but it seems to have a success rate of around 80%.

Dim totalRange As RangeDim cell As Range


sumws.Rows("13:42").Hidden = False




    For Each cell In ActiveSheet.Range("F13:G42")
       If cell = "" And totalRange Is Nothing Then
            Set totalRange = cell
       ElseIf cell = "" Then
            Set totalRange = Application.Union(totalRange, cell)
       End If
    Next
    
    If Not totalRange Is Nothing Then
        totalRange.EntireRow.Hidden = True
    End If