How do I use cells to reference a range when using Autofilter in Excel VBA - it works with A1:Z & lastrow but not cell(1,1),Cell(x,y)

Question

I have some code which uses and autofilter on a pair of variables to filter the contents of the sheet.

'With Worksheets("Raw SC data").Range("$A$1:$z$" & raw_LR)  'this needs to go further than Z!!!
    .AutoFilter Field:=5, Criteria1:=Crit1
    .AutoFilter Field:=Crit2, Criteria1:="<>"
End With  ```

The issue is that I don't know how many columns of data will come over, so column Z is too few. I tried using With Worksheets("Raw SC data").Range("$A$1:$bz$" & raw_LR) however this caused an error out so I assume autofilter doesn't like empty headings?

I have some code which counts the rows (244) and columns (52) and I thought I could use cells() to make a variable range

With Worksheets("Raw SC data").Range(Cells(1, 1), Cells(raw_LR, data_cols))
    .AutoFilter Field:=5, Criteria1:=Crit1
    .AutoFilter Field:=Crit2, Criteria1:="<>"
End With

However this just errors out with 438 - Object doesn't support this property or method

I just know that I am doing something stupid but what?

No repro on Error 438, but this will definitely fail if the `Raw SC data` worksheet is not active. You need to qualify the inner `Cells` calls with the worksheet. — BigBen, Mar 09 '23 at 15:29
You can use Resize -`With Worksheets("Raw SC data").Range("A1").Resize(raw_LR, data_cols)` — CDP1802, Mar 09 '23 at 15:47

How do I use cells to reference a range when using Autofilter in Excel VBA - it works with A1:Z & lastrow but not cell(1,1),Cell(x,y)

0 Answers0