If filtered table shows no result macro

[mark692]

Is there a code for filtering a table? If the code filtered a table and shows no result the code will do nothing, but if the filtered table has a result it will select the result, is that possible? Thanks

 

[Akuini]

1. What do you mean by "the code will do nothing"? Cancel the filter (i.e show all data)?
2. Is your table a real table (not just range)?

Maybe this:

[FONT=lucida console][COLOR=Royalblue]With[/COLOR] ActiveSheet.ListObjects([COLOR=brown]"Table1"[/COLOR])
        .Range.AutoFilter Field:=[COLOR=crimson]1[/COLOR], Criteria1:=[COLOR=brown]"h"[/COLOR]
     [COLOR=Royalblue]If[/COLOR] .Range.Columns([COLOR=crimson]1[/COLOR]).SpecialCells(xlCellTypeVisible).Cells.count = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        .Range.AutoFilter
     [COLOR=Royalblue]Else[/COLOR]
        .DataBodyRange.Select
     [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR][/FONT]

 

[Domenic]

Assuming that Sheet1 contains your table, and that your table is named Table1, try...

Option Explicit

Sub SelectFilteredRecords()


    Dim rngFilter As Range


    With Worksheets("Sheet1")
        If .FilterMode Then .ShowAllData
    End With


    With Worksheets("Sheet1").ListObjects("Table1").Range
        .AutoFilter field:=1, Criteria1:="x"
        On Error Resume Next
        Set rngFilter = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rngFilter Is Nothing Then
            rngFilter.Select
        End If
    End With
    
End Sub

Change the sheet name and table name, accordingly. Also, change the field and criteria for your filter, accordingly.

Hope this helps!

 

[mark692]

Assuming that Sheet1 contains your table, and that your table is named Table1, try...

Option Explicit

Sub SelectFilteredRecords()


    Dim rngFilter As Range


    With Worksheets("Sheet1")
        If .FilterMode Then .ShowAllData
    End With


    With Worksheets("Sheet1").ListObjects("Table1").Range
        .AutoFilter field:=1, Criteria1:="x"
        On Error Resume Next
        Set rngFilter = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rngFilter Is Nothing Then
            rngFilter.Select
        End If
    End With
    
End Sub

Change the sheet name and table name, accordingly. Also, change the field and criteria for your filter, accordingly.

Hope this helps!

this work sir but can you add a code that will bring me to ''sheet 2'' if the filtered table has no result. thanks

 

[Domenic]

Try replacing...

        If Not rngFilter Is Nothing Then
            rngFilter.Select
        End If

with

        If Not rngFilter Is Nothing Then
            rngFilter.Select
        Else
            .AutoFilter 'clear filter (optional)
            Worksheets("Sheet2").Activate
        End If

 

[mark692]

Try replacing...

        If Not rngFilter Is Nothing Then
            rngFilter.Select
        End If

with

        If Not rngFilter Is Nothing Then
            rngFilter.Select
        Else
            .AutoFilter 'clear filter (optional)
            Worksheets("Sheet2").Activate
        End If

thank you sir it work sorry for late reply

 

[Domenic]

You're very welcome! Thanks for the feedback!

Cheers!