Dynamic Ranges for Filter Macros

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
151
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.
 
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).
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
In that case try
Code:
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
 
Upvote 0
In that case try
Code:
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?)
 
Upvote 0
You can only Autofilter for 2 values unless looking for an exact match.
You might want to look at using Advanced filters instead.
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top