Autofilter with Variables

MAM8433

New Member
Joined
May 18, 2011
Messages
44
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
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.
 
Upvote 0
Is the data in a structured table?
 
Upvote 0
In that case what is the name of the table & what is the name of the two columns to filter?
 
Upvote 0
The table has a named range "Claims." The BCO data column header is "BCO Match" and the BCO_Excl column is "BCO Excl Hosp."
 
Upvote 0
I need to know the name of the table, not a named range.
 
Upvote 0
Sorry. I use 365 and when I click on the Name Manager in the Formula tab, it lists "Claims" as the name.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top