Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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"?

UPDATE: This is the full statement

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

where Field:=4 is actually the Field Exclude in that table i.e. tPDJuly[Exclude] and similarly Field:=2 is Grade

So I want to change these fixed references to 2 and 4 into something more flexible.

share|improve this question

You should be able to select a column using the table properties with the following:

sheets("Plot Data July").select    

This should select your grade column. The quotes seem to be important when concantenating the values into a reference.

If you want both columns, you'll need to change it a bit:

sheets("Plot Data July").select    
share|improve this answer
So then that would be Field:=range("tPDJuly[Grade]").select ? – Eshwar Sep 18 '12 at 5:58
You shouldn't need Field: at all. – dav Sep 18 '12 at 12:22
Then the solution would be ActiveSheet.ListObjects("tPDJuly").Range.AutoFilter range("tPDJuly[Grade],tPDJuly[Column4]").select ? – Eshwar Sep 19 '12 at 15:48
When I tried it on my test worksheet, I didn't need anything other than range("tPDJuly[Grade],tPDJuly[Column4]").select if I was on the sheet with the table. Otherwise, I needed to add sheets("Plot Data July").select also. I never needed to use activesheet.listobjects().range.autofilter at all. – dav Sep 20 '12 at 12:18
Nope, doesn't work when you want to use criteria. Did you check the update to the question? – Eshwar Sep 21 '12 at 13:32

Dragging this thread back into life. I've been trying to achieve something myself today and through brute force and trial and error, I've found an answer that doesn't involve a messy select statement or having an activated sheet.

TableName.Range.Autofilter Field:=TableName.ListColumns("Column Name").Index, _
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.