VBA - Hard Coded to Cell Reference

[itsgrady]

In the bold area, I would like to make a cell references instead of hard coding. Is this possible to have about 10 cells VBA will reference instead of the actual criteria in the code? The criteria changes a lot more than what was first mentioned when the VBA code was built.

Sub SpecialDMann()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("VPI-All-WhsRpt")

Application.ScreenUpdating = False
If ws.AutoFilterMode Then
ws.AutoFilterMode = False

End If

Sheets("VPI-All-WhsRpt").Activate

ws.ListObjects("VPI_All_WhsRpt").AutoFilter.ShowAllData

ws.ListObjects("VPI_All_WhsRpt").Range.Select

ws.ListObjects("VPI_All_WhsRpt").Range.AutoFilter 22, Array("4A Back", "4B Back", "4C Back", "4D Back", "4A Middle", "4B Middle", "4C Middle"), Operator:=xlFilterValues

Set rng = ws.Range("A10")
rng.Select
rng.Application.Goto rng, True
Set rng = Nothing
Set ws = Nothing

Application.ScreenUpdating = True

End Sub

 

[Joe4]

Check out this link on a similar question, creating an array from a range to use in a Filter in VBA.

Excel VBA Range To Array When Single Cell

Hi all, Using Excel 365. I need to transfer a Range based on user inputs to an Array to be used as Filter Criteria. My challenge is that the user may select from 1 to n items from a list. I had code working for multiple items in the Range, but it wasn't working for a Range that was a single...

www.mrexcel.com

And another article which may help:

Excel VBA Assign Range to Array - wellsr.com

Discover how easy it is to assign a range to an array using Excel VBA. You will also learn how to avoid common errors, like run-time error 13 type mismatch.

wellsr.com