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]

The workbook I'm running the Filter on is the active workbook and the code for the Macro is in PERSONAL.XLSB. Its the list of variables (sheet FilterValues)I want to apply to the filter that I was hoping to keep in PERSONAL.XLSB as I cant store in the Workbook File I'm running the filter on or preferably not in another seperate excel file. If I cant store in one of the worksheets in PERSONAL.xlsb I suppose I will have to.

 

[Fluff]

You can store the list in your personal.xlsb file, just change the sheet name in red to suit

With ThisWorkbook.Sheets("[COLOR=#ff0000]List[/COLOR]")

 

[steveh8204]

You can store the list in your personal.xlsb file, just change the sheet name in red to suit

With ThisWorkbook.Sheets("[COLOR=#ff0000]List[/COLOR]")

But I don't know how to open the blank worksheet in the PERSONAL.XLSB file to put the values in. I can only access the Macro part through the VBA window and not the PERSONAL.XLSB file in the normal Excel window.

 

[Fluff]

I got you.
On the View tab select Unhide, then select Personal.xlsb & OK
You can then use the file like any normal file, but you may want to hide it again, otherwise will it always be visible.

 

[steveh8204]

I got you.
On the View tab select Unhide, then select Personal.xlsb & OK
You can then use the file like any normal file, but you may want to hide it again, otherwise will it always be visible.

Ooo nice one. Handy little tip that, will come in very handy.

Thanks and for all your help with this issue.

 

[Fluff]

My pleasure & thanks for the feedback

 

[steveh8204]

Something like

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

Right I've gone back to struggling with the basics now haha. I've tried a few different combinations of references because I didn't want to pester again but I'm having no joy.

In the code you posted above I'm struggling with what refers to what. If I show you what I'm after are you able to clarify please?

The active sheet I am working on in "Sheet1" has the year references in column C and a SKU reference in Column A.

My "List" sheet in my PERSONAL.XLSB file has a table with the Year Ref in Column A and the SKUs in Column B, I want to filter all the rows out in the Active Worksheet by the values in these 2 columns (A and B in PERSONAL.XLSB).

I have tried the following code but my references are obviously wrong as it filters out everything:

Sub steveh8204()
   Dim Ary1 As Variant, Ary2 As Variant
   
   With Workbooks("PERSONAL.XLSB").Sheets("List")
      Ary1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2 ' Year
      Ary2 = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2 ' SKU
   End With
   With ThisWorkbook.Sheets("Sheet1")
      .Range("A1:Z1").AutoFilter 1, Application.Transpose(Ary2), xlFilterValues
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary1), xlFilterValues
   End With
End Sub

 

[Fluff]

ThisWorkbook refers to the workbook that contains the macro, so in this case that's the personal.xlsb, so you need to use

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 8, Application.Transpose(Ary1), xlFilterValues
      .Range("A1:Z1").AutoFilter 5, Application.Transpose(Ary2), xlFilterValues
   End With
End Sub

And if you want it to work on whichever sheet is active, rather than just sheet1 you can change

Wit ActiveWorkbook.Sheets("Sheet1")

to

With ActiveSheet

 

[steveh8204]

ThisWorkbook refers to the workbook that contains the macro, so in this case that's the personal.xlsb, so you need to use

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 8, Application.Transpose(Ary1), xlFilterValues
      .Range("A1:Z1").AutoFilter 5, Application.Transpose(Ary2), xlFilterValues
   End With
End Sub

And if you want it to work on whichever sheet is active, rather than just sheet1 you can change

Wit ActiveWorkbook.Sheets("Sheet1")

to

With ActiveSheet

I'm still struggling with this, I'm not getting any results no matter what I try. I have put both sheets in the same file and still no joy.

Would you please be able to look at this and tell me what I am doing wrong:

https://www.dropbox.com/s/qzzihbciv9tk26c/Book1.xlsm?dl=0

 

[Fluff]

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?