VBA criteria to reference cell in workbook

Hi,

I have the below code and I would like to have the filter that current looks for the string 'Alpha Region' to be dynamic and refer to cell B1 on the tab RegionalAnalysis. Currently it is just referring to a text string - is there anyway to make this dynamic?

HTML Code: 

Any help greatly appreciated

Hello Tony,

You could just change the Criteria1 as follows:-

Firstly declare a variable such as:-

Dim Crit1 as string

then give the variable a value:-

Crit1 = sws.Range("B1").Value

then filter on Crit1

.AutoFilter 3, Crit1

You should then be able to filter on whatever value that you place in B1 (from a drop down perhaps?).

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio,

Thank you - for some reason I had to take the check off if there was any data displayed after Autofiltering as it was only working for some regions but not others - but I can look at that later.

Thanks for your help!

You're welcome Tony. I'm glad to have been able to assist.

Generally, using the Autofilter within a "With" statement, should negate the need for a check. A couple of examples follow:-

Sub FilterIt()

Application.ScreenUpdating = False

        Dim sws As Worksheet, dws As Worksheet, Crit1 As String
        
        Set sws = Sheets("RegionalAnalysis")
        Set dws = Sheets("RegionalCustomersData")
        Crit1 = sws.Range("B1").Value
        
dws.Cells.Clear

        With sws.Range("C5", sws.Range("C" & sws.Rows.Count).End(xlUp))
                .AutoFilter 1, Crit1
                Range(.Offset(, -2), .Offset(, 6)).Copy dws.Range("A2")
                .AutoFilter
        End With
        
Application.ScreenUpdating = True

End Sub

...or, if there is at least one completely empty row directly above the dataset (i.e. Row 4), then this should work as well:

Sub FilterIt()

Application.ScreenUpdating = False

        Dim sws As Worksheet, dws As Worksheet, Crit1 As String
        
        Set sws = Sheets("RegionalAnalysis")
        Set dws = Sheets("RegionalCustomersData")
        Crit1 = sws.Range("B1").Value
        
dws.Cells.Clear

        With sws.[A5].CurrentRegion
                .AutoFilter 3, Crit1
                .Columns("A:F").Copy dws.Range("A2")
                .AutoFilter
        End With
        
Application.ScreenUpdating = True

End Sub

Thanks for the rep!

Cheerio,
vcoolio.