Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

sheets("Plot Data July").select    
range("tPDJuly[Grade]").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    
range("tPDJuly[Grade],tPDJuly[Column4]").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

Your Answer

 
discard

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.