This is the challenge I'm facing:
I want to create macro's which are intended to pre-autofilter data in a sheet. However, if new columns get insterted or re-arranged (it's a living file), all macro's need to be changed again since the columns get shifted and so does the FIELD:= value of the Autofilter command. I want to keep the macro's flexible by making the Autofilter use relative Range names instead of 1 for column A, etc...
Current Example (filter col B on YES)
Selection.AutoFilter Field:=2, Criteria1:="YES"
Desired Example (filter name defined Range on YES)
Selection.AutoFilter Field:=RANGENAME, Criteria1:="YES"
With the RANGENAME I want to define a range like cell B2 until B10 [=Sheet1!$B$2:$B$10]
I think I have to define a variable with DIM, but am getting stuck in my trail and error process... any help is really aprreciated!
Thanks,
BA
Bookmarks