How can I use structured references to a column in an Excel macro?

Posted by Eshwar on Super User See other posts from Super User or by Eshwar
Published on 2012-09-17T09:05:48Z Indexed on 2012/09/17 9:40 UTC
Read the original article Hit count: 339

Filed under:
|

Here's an example that will explain things:

Sheets("Plot Data July").Select
    ActiveSheet.ListObjects("tPDJuly").Range.AutoFilter Field:=2
    ActiveSheet.ListObjects("tPDJuly").Range.AutoFilter Field:=4

So as you can see above, Field:=2 is a relative reference to the second field in the table called "tPDJuly". So now if I add more columns, this number does not get updated. The field is actually called "Grade" in the table. So is there a way of coding this so that no matter which column it is in, "Grade" is always updated?

I suppose one solution is that we add a line that find what is the column number for "Grade"?

© Super User or respective owner

Related posts about excel-2010

Related posts about vba