Autofilter with Variables

MAM8433

New Member
Joined
May 18, 2011
Messages
35
I want to filter the column that contains BCO data to “yes” and the BCO_Excl column to “out.” This code was working and now doesn’t, no doubt due to numerous edits.

Because the exact location of the BCO data is not static, variable intBCOCol contains the column number (21 in my case). Likewise, intBCOExcCol contains the BCO_Excl column number (its value is 25).

In my file, the column headers are in row 8. IntLastRowN is the number of the last data row (which is 2477); intLastCol contains the last used column (column 26). My code is:

With Worksheets("Provider").Range(Cells(8, 1), Cells(intLastRowN, intLastCol))

.AutoFilter Field:=intBCOCol, Criteria1:="Y"

.AutoFilter Field:=intBCOExcCol, Criteria1:="OUT"

End With



The code chokes on the second line. The message is: Run-time error ‘1004’: Autofilter method of range class failed.” However when I hover over intBCOCol, it shows that the value is 21, as expected.

Does this mean that the first line of code is incorrect? I read that variables can be used in cell addresses but not in ranges. I hope you can help me as the more I research these issues, the more confused I get.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
With Worksheets("Provider")
   With .Range(.Cells(8, 1), .Cells(intLastRowN, intLastCol))
      .AutoFilter Field:=intBCOCol, Criteria1:="Y"
      .AutoFilter Field:=intBCOExcCol, Criteria1:="OUT"
   End With
End With
Your code would only work if the Provider sheet was the active sheet, as you hadn't qualified the Cells with the sheet.
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35
Thanks. The code still doesn't work. The worksheet where I want to filter is active. Prior to this task, I ran vlookups and pasted special. The BCO data and BCO_ Excl columns were included in that code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Is the data in a structured table?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
In that case what is the name of the table & what is the name of the two columns to filter?
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35

ADVERTISEMENT

The table has a named range "Claims." The BCO data column header is "BCO Match" and the BCO_Excl column is "BCO Excl Hosp."
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
I need to know the name of the table, not a named range.
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35
Sorry. I use 365 and when I click on the Name Manager in the Formula tab, it lists "Claims" as the name.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
With Sheets("Provider").ListObjects("claims")
   .Range.AutoFilter .ListColumns("BCO Match").Index, "Y"
   .Range.AutoFilter .ListColumns("BCO Excl Hosp").Index, "OUT"
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,119,255
Messages
5,576,981
Members
412,757
Latest member
Thalalala
Top