Filtering Data based on <>

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,838
Office Version
  1. 365
Platform
  1. Windows
I have this code that works. But if for some reason someone adds or deletes a column (which they shouldn't do, but it happens) the code will not work properly

Is there any way to change it so it looks for a specific field in the table to do the filter.

This is a Table that's being filtered (tblSQD). In the code I show the names of the equivalent fields

Code:
Sub ApplyFilters()
'SQD Tabe is Filtered based on ItemID and Dates set on Search tab

        
    Sheets("SQD").Activate
        Range("A6").Activate
        
    'Reset any previous filters
    If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If


    'Filter SQD to Match Search
    
        'Table Field: ItemID_Match
        ActiveSheet.ListObjects("tblSQD").Range.AutoFilter Field:=83, Criteria1:= _
            "<>"
        
        'Table Field: RFQDate_Match
        ActiveSheet.ListObjects("tblSQD").Range.AutoFilter Field:=84, Criteria1:= _
            "<>"
        
        'Table Field: QuoteDate_Match
        ActiveSheet.ListObjects("tblSQD").Range.AutoFilter Field:=85, Criteria1:= _
            "<>"
        
        'Table Field: QuoteExpiration_Match
        ActiveSheet.ListObjects("tblSQD").Range.AutoFilter Field:=86, Criteria1:= _
            "<>"
        
    ActiveSheet.Range("A5").Select

End Sub

Thanks for the help
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
You can do it like
VBA Code:
With ActiveSheet.ListObjects("tblSQD")
   .Range.AutoFilter .ListColumns("ItemID_Match").Index, "<>"
   .Range.AutoFilter .ListColumns("RFQDate_Match").Index, "<>"
End With
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,132
Messages
5,628,891
Members
416,349
Latest member
salmanb74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top