Dynamic Ranges for Filter Macros

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
143
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You could do something like
Code:
Sub steveh8204()
   Dim Ary As Variant
   
   With Workbooks("Book2.xlsm").Sheets("List")
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   With ThisWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary), xlFilterValues
   End With
End Sub
 
Upvote 0
You could do something like
Code:
Sub steveh8204()
   Dim Ary As Variant
   
   With Workbooks("Book2.xlsm").Sheets("List")
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   With ThisWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary), xlFilterValues
   End With
End Sub

Nice one, works perfect, thank you!

Don't suppose you know how to make it work on 2 filters within the table? :)
 
Upvote 0
Something like
Code:
Sub steveh8204()
   Dim Ary1 As Variant, Ary2 As Variant
   
   With Workbooks("Book2.xlsm").Sheets("List")
      Ary1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
      Ary2 = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2
   End With
   With ThisWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary1), xlFilterValues
      .Range("A1:Z1").AutoFilter 5, Application.Transpose(Ary2), xlFilterValues
   End With
End Sub
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Something like
Code:
Sub steveh8204()
   Dim Ary1 As Variant, Ary2 As Variant
   
   With Workbooks("Book2.xlsm").Sheets("List")
      Ary1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
      Ary2 = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2
   End With
   With ThisWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary1), xlFilterValues
      .Range("A1:Z1").AutoFilter 5, Application.Transpose(Ary2), xlFilterValues
   End With
End Sub

Is it possible to create the lists for these macros in PERSONAL.XLSB? I was hoping to use this Macro without having a seperate spreadsheet open at the same time (I always have enough Windows open as it is in work) so was hoping to use PERSONAL.XLSB as its always open, well at least for Macros it is anyway. I tried opening the file but it doesnt open. Can it only be used for Macros?

I cant store the lists in the files I use to filter as they are company wide documents that are controlled from afar so its not really feasible.

Thanks in advance.
 
Upvote 0
Do you have the code in your Personal.xlsb file?
 
Upvote 0
If the code is in your Personal.xlsb file try
Code:
Sub steveh8204()
   Dim Ary1 As Variant, Ary2 As Variant
   
   With ThisWorkbook.Sheets("List")
      Ary1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
      Ary2 = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2
   End With
   With ActiveWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary1), xlFilterValues
      .Range("A1:Z1").AutoFilter 5, Application.Transpose(Ary2), xlFilterValues
   End With
End Sub
The workbook you want to filter needs to be the active workbook.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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