Selecting a Range in filtered data - Excel VBA

Question

I have a macro that AutoFilters a range & filters for criteria successfully. I am looking to copy-visible this resulting range, and paste it elsewhere, but am having trouble selecting this filtered-range. Row 1 of my data is a header.

Here is the code snippet, applied to the filtered data:

Set mainsheet = Workbooks("MyFile.xlsm").Sheets("Main")
Range("A1").Select

With mainsheet
.Range(.Cells(Selection.Row + 1, 1), .Cells(Selection.Row + 1, 47)).Select
End With

I figured this would move the cursor down to the next visible cell, but it actually moves down to cell A2 (which is filtered out).

I need to select the first row of data not including my header, so I can xlDown and copy, but I can't figure out how to increment downward. Any tips?

I've just realized that utilizing SpecialCells(xlCellTypeVisibile).Select would mitigate this issue, as the filtered cells are obviously not visibile. But I am still interested in the answer to this & what the "correct" way to select a range when using autofiltered data. — Alex
As you say, just copy the visible cells. As a basic example of copying from a filtered list where the whole list covers cells A2:W235 I'd use something similar to: Sheet1.Range("A2:W235").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet3.Range("A1") — Darren Bartrup-Cook

paul bica paul bica · Accepted Answer · 2017-06-12T16:24:28

Use this notation

With mainsheet.UsedRange
    .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
End With

.Offset(1) moves the entire selection one row down (exclude the headers)

.Resize(.Rows.Count - 1) removes the last (extra) row selected by the offset

Selecting a Range in filtered data - Excel VBA

2 Answers