3

What I want to do with VBA - filter table with array and delete rows. My array has 4 elements (changed in a loop to have 5 different sets). Column which is being filtered has 5 elements. I want to get just 1. This is inside a loop which will create 5 reports, each filtered for different element based on column 29.

If in debugging mode I filter that column manually and skip that line I have second, identical error 5 lines below when trying to clear all filters back again.

This are lines 765 and 770 out of part of 788. Everything else runs smoothly.

FilterOutArray - array with 4 elements (Variant/String each). Column 29 has 5 different values, no blanks

I've tried to refer "Criteria1" with string, to see if filtering one elements works, but not.

'Run-time error: '1004' on row below
ActiveSheet.Range(Cells(1, 1).Cells(29, LastRow)).AutoFilter Field:=29, Criteria1:=FilterOutArray, Operator:=xlFilterValues

Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

'Run-time error: '1004' on row below
ActiveSheet.Range(Cells(1, 1).Cells(29, 9999)).AutoFilter Field:=29
Bartek Nowakowski
  • 271
  • 3
  • 12
  • 3
    what do you expect by `Range(Cells(1, 1).Cells(29, LastRow))`? Shouldn't it be `Range(Cells(1, 1), Cells(29, LastRow))`? – Pᴇʜ Sep 09 '19 at 15:38

1 Answers1

7

In the line:

ActiveSheet.Range(Cells(1, 1).Cells(29, LastRow)) ...

Should be a comma, and not a dot. Like this:

ActiveSheet.Range(Cells(1, 1), Cells(29, LastRow)) ...

The "interesting" part is that something like this: Cells(1,1),Cells(2,2).Address is a valid syntax, giving the address of the corresponding cell, 1 column right and 1 row down, e.g. B2.

Anyway, in general, consider How to avoid using Select in Excel VBA. And the "correct" way to write ranges defined by 2 cells is something like this:

With Worksheets(1)
    Set myRange = .Range(.Cells(1, 1), .Cells(5, 5))
End With
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 2
    Will make a comment on rather than posting an answer... qualify all references, Bartek. If `ActiveSheet.Range()` were to read `Sheets(1).Range()`, you'd be in a slightly different scenario, since `ActiveSheet` is inferred by the system for each of your `cells()` references... `Sheets(1).Range(Sheets(1).Cells(),Sheets(1).Cells())` would be appropriate, though a `With-statement` typically makes it look cleaner. – Cyril Sep 09 '19 at 15:44