VBA - filtering columns after inserting or deleting cells

[jon1315]

Good afternoon

I'm looking for a way to make the field of an autofilter dynamic - for example:

Range("a1:J" & LRow ).autofilter Field:=7, Criteria1:=""

If someone were to add a column between B&C, the field would now need to be 8, but the code won't register this.

I've thought about maybe doing something like:

Dim ColRef as long
ColRef=worksheetfunction.match( [whatever col G header ref is].sheets("sheet1"). Range("A1:J1).0)

But as I'll be putting quite a few filters at various places in the code I'm wondering if there's another way?

 

[JLGWhiz]

Yes, you would need to use a variable for the target column that can locate a string that will constantly be in the target column.
Assume "myHeader" as the string in row 1 of a tartet column

Dim col As Long
col = Rows(1).Find("myHeader", , xlValues).Column
ActiveSheet.UsedRange.AutoFilter col, Citeria

 

[jon1315]

Thanks for the reply
Would you say that'd be going down the match route?
Thanks

 

[JLGWhiz]

I didn't get the code added quick enough. See my first post.

 

[jon1315]

Having a play with it now - looks to work great! Thank you!

 

[JLGWhiz]

Having a play with it now - looks to work great! Thank you!

You're welcome,
regards, JLG