CODEWITHSUNDEEP
excelvba
Jaisri Rajan
Jaisri Rajan

Reputation: 1

Excel VBA : Find out which row has filter enabled

I am looking at various options in VBA to find out the row number in a worksheet that has filter enabled.

If ThisWorkbook.Sheets(1).AutoFilterMode = True Then

The above line checks only if the sheet contains filters, but I need to know which row number has the filters on.

Upvotes: 0

Views: 2831

Answers (2)

anefeletos
anefeletos

Reputation: 702

This is a straight answer to your question:

Function CheckWhichRowHasFilter(r As range)
For Each rowi In r.Rows' Dim rowi As range
Set pa = rowi.Parent.AutoFilter'Dim pa As AutoFilter
If pa.FilterMode = True Then
  CheckWhichRowHasFilter = pa.range.Address
    Exit For
End If
Next rowi
End Function

This is how to iterate through Filters. The Property you want to check is Filter.On

Sub IterateThroughFilters()
Dim r As range
Set r = Selection
Dim rc As range
For Each rc In r.Columns
   If Not rc.Parent.AutoFilter Is Nothing Then
    Set currentColumnFilter = rc.Parent.AutoFilter ' Filteraddress: = currentColumnFilter.range.Address
       Dim ccf As filters
       Set ccf = currentColumnFilter.filters
         Dim cf1 As filter
         Set cf1 = ccf(1) 'onebased index
             If cf1.On Then 'Here you check if filter is on

                cfc1 = cf1.Criteria1(1)
                cfc2 = cf1.Criteria1(2)
                cfc3 = cf1.Criteria1(3)
            End If
   End If

Next rc

End Sub

Upvotes: 0

Jur Pertin
Jur Pertin

Reputation: 564

This should do your job.

 Sub test()

    Dim rngRange        As range

    If ThisWorkbook.Sheets(1).AutoFilterMode = True Then
        Set rngRange = ThisWorkbook.Sheets(1).AutoFilter.range
        MsgBox "Address of Filter: " & rngRange.Address & Chr(10) _
                & "Row Number is: " & rngRange.Row, vbOKOnly
    End If

    Set rngRange = Nothing

End Sub

Upvotes: 1

Related Questions