Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .