Dynamic Ranges for Filter Macros

steveh8204

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
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
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
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? :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
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
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
Nice one, thank you very much, and for the quick reply!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

steveh8204

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
Do you have the code in your Personal.xlsb file?
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
Do you have the code in your Personal.xlsb file?
I can get the Macro code in there easy enough. Its just opening the PERSONAL.XLSB sheet to put the varaibles in that the filter needs to work on.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,085,580
Messages
5,384,560
Members
401,908
Latest member
shaanprakash

Some videos you may like

This Week's Hot Topics

Top