VBA: Test if excel autofilter returned anything

[excelpinto]

Hey all,

I'm using the autofilter to dynamically sort some data. Here's what I'm looking to do: If I apply the filter, and I get at least one filtered row (doesn't matter how many or what it is), I apply some formatting. If criteria applied to the autofilter results in no rows returned, no formatting.

I just dont know how to check for the presence of any row being returned after the criteria is applied? Any ideas/examples? Thx.

 

[excelpinto]

Got it

With ActiveSheet. Range("A1").Offset(1, 0)
If Not (.Rows.Hidden) Then
Debug.Print .Value
End If
End With

 

[stanleydgromjr]

excelpinto,

Sample data before the macro:

Excel Workbook
	A	B
1	Title A	Title B
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	1	1
8	2	2
9	3	3
10	4	4
11	5	5
12	1	1
13	2	2
14	3	3
15	4	4
16	5	5
Sheet1

After the macro:

Excel Workbook
	A	B
1	Title A	Title B
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	1	1
8	2	2
9	3	3
10	4	4
11	5	5
12	1	1
13	2	2
14	3	3
15	4	4
16	5	5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module.

Option Explicit
Sub Test()
    Dim FilteredRange As Range
    Dim rw As Range
    Application.ScreenUpdating = False
    With ActiveSheet.Range("A1")
        .AutoFilter Field:=1, Criteria1:="2"
        Set FilteredRange = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
        For Each rw In FilteredRange.Rows
            If rw.Row > FilteredRange.Rows.Row Then
                'If visible cell, format row here
                With rw
                    .Font.FontStyle = "Bold"
                    .Interior.ColorIndex = 3
                End With
            End If
        Next
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Then run the "Test" macro.


Have a great day,
Stan

 

[ZVI]

More correctly relative to yours:

<font face=Courier New>
Function FilterOk(Sh As Worksheet) As Boolean
  On Error Resume Next
  With Sh.AutoFilter.Range
    FilterOk = .SpecialCells(xlCellTypeVisible).Rows.Address <> .Rows(1).Address
  End With
End Function

Sub Test_FilterOk()
  Debug.Print FilterOk(ActiveSheet)
End Sub
</FONT>

 

[excelpinto]

That's great ZVI. Its exactly what I need. Thank you as well Stanley. Both useful tricks. Thx again.