Basic question on VBA autofilter "Field" code.

[Jagdev Singh]

Hi Experts

I just want to know if the Field code in the VBA autofilter can be changes to name of the column say "Name" instead of the number of the column. I googled it and find that the data type of it is Variant.

ActiveSheet.ListObjects("Table").Range.AutoFilter Field _
        :=25

Regards,
JD

 

[Deepak]

You would try

Dim c As Long
c = 25
ActiveSheet.ListObjects("Table").Range.AutoFilter Field:=c

 

[Jagdev Singh]

Hi Deepak

It is throwing an error - 1004 - AutoFilter method of Range class failed.

Regards,
JD

 

[Smallman]

Here is an example workbook with coding.

Sub TestFilter()
Dim iCol As Long
iCol = 25
    [A1].CurrentRegion.AutoFilter iCol, 1
End Sub

No errors.

Take care

Smallman

 

[Deepak]

Hi Deepak

It is throwing an error - 1004 - AutoFilter method of Range class failed.

Regards,
JD

because your table name is wrong.

ActiveSheet.ListObjects("Table1").Range.AutoFilter c, "ABC"

or check what Smallman posted.

 

[NARAYANK991]

Hi Jaggi ,

See these two links for more information :

http://www.mrexcel.com/forum/excel-questions/461431-can-i-name-autofilter-field.html

http://www.pcreview.co.uk/threads/set-autofilter-field-in-excel-to-a-range-name.942183/

Narayan

 

[Jagdev Singh]

Hi All,

Thanks for your inputs.

@ Narayan - Thanks for the link - http://www.mrexcel.com/forum/excel-questions/461431-can-i-name-autofilter-field.html

This is what I was looking for..Thanks again Sir for clearing my random doubt.

Regards,
JD