Dynamic Ranges for Filter Macros

[steveh8204]

Hi,

I've created several different Macros for Filtering a spreadsheet for defined values (SKUs) but occasionally these values change. I was wondering is possible to create a macro in VBA that filters a spreadsheet based on all the values in a column on a different sheet in a different file?


I could then change the values in the relevant column as opposed to delving into the code each time to change/add/delete them.


Other people maintain the spreadsheet too so asking them to delve into VBA code isn't going to work but just updating a list will.

Thanks in advance.

 

[steveh8204]

In that file everything is numeric, will that be the case for your real data?
Also will you have just 1 value to filter on?

The real file will be numeric too (both fields) and it needs to be two values to filter on, the year and the SKU. There is only one year in the List but it will sometimes be two (towards the end of the year). That's part of the reason I need a dynamic table list (and ever changing SKUs).

 

[Fluff]

In that case try

Sub steveh8204()
   Dim Ary As Variant
   Dim Crit1 As Long, Crit2 As Long
   Dim i As Long
   
   With ThisWorkbook.Sheets("List")
      Crit1 = .Range("A1").Value
      Crit2 = .Range("A2").Value
      Ary2 = Application.Transpose(.Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2)
   End With
   For i = 1 To UBound(Ary2)
      Ary2(i) = CStr(Ary2(i))
   Next i
   With ActiveSheet
      .Range("A1:e1").AutoFilter 3, Crit1, xlOr, Crit2
      .Range("A1:e1").AutoFilter 1, Ary2, xlFilterValues
   End With
End Sub

 

[steveh8204]

In that case try

Sub steveh8204()
   Dim Ary As Variant
   Dim Crit1 As Long, Crit2 As Long
   Dim i As Long
   
   With ThisWorkbook.Sheets("List")
      Crit1 = .Range("A1").Value
      Crit2 = .Range("A2").Value
      Ary2 = Application.Transpose(.Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2)
   End With
   For i = 1 To UBound(Ary2)
      Ary2(i) = CStr(Ary2(i))
   Next i
   With ActiveSheet
      .Range("A1:e1").AutoFilter 3, Crit1, xlOr, Crit2
      .Range("A1:e1").AutoFilter 1, Ary2, xlFilterValues
   End With
End Sub

Nice one. Works perfect now. Thanks!

Had to tinker a little bit so I could put headings in for my list table but its all good now pretty much.

Is there a way to use "Wildcards" when looking up values from the table as sometimes only part of the value matches. Basically the last one digit is different sometimes so I was hoping to put the full SKU in the reference table ("PERSONAL.XLSB.List") minus the last digit and then when the filter runs in the active worksheet it will look for values where most of the digits match (the first 9 digits?)

 

[Fluff]

You can only Autofilter for 2 values unless looking for an exact match.
You might want to look at using Advanced filters instead.

 

[steveh8204]

You can only Autofilter for 2 values unless looking for an exact match.
You might want to look at using Advanced filters instead.

Tbh I'll manage without. I'll work with this as it's more all round suited to my purposes.

Thanks again for all your help with this.

 

[Fluff]

You're welcome & thanks for the feedback