Fix Excel VBA AutoFilter Missing the Last Rows When Copying Data
Question details
The user needs an Excel VBA macro to copy all rows that match an AutoFilter, including the final rows.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A macro determines a data range and uses SpecialCells to copy its visible filtered rows.
- Observed behavior
- The copied output is missing the final rows, potentially because blank cells or noncontiguous data cause the macro to calculate a range that is too short.
Identify the source and destination sheet names, the header row, and the columns that contain your data. Check that the applied filter covers the full data range and that the missing rows actually match the filter criteria.
Find the Last Data Row and Copy the Full Filtered Range
Use this approach when blank cells make Ctrl+Down or COUNTA stop or count too early.
The example searches columns A through D for the last occupied row, then copies visible cells from that range, including the header. Adjust the columns and header row to match your worksheet.
The search includes cells containing formulas, even if a formula displays an empty string. SpecialCells also excludes manually hidden rows.
In Excel, open the workbook and use Developer > Visual Basic. Open the module containing the macro, or choose Insert > Module to test the example.
Replace the sheet names and A:D column range in this example with your own, then use it in place of the existing range and copy logic: Sub CopyFilteredRows() Dim src As Worksheet, dst As Worksheet Dim lastCell As Range, dataRange As Range Dim lastRow As Long Set src = ThisWorkbook.Worksheets("Sheet1") Set dst = ThisWorkbook.Worksheets("Sheet2") Set lastCell = src.Range("A:D").Find(What:="*", LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False) If lastCell Is Nothing Then Exit Sub lastRow = lastCell.Row Set dataRange = src.Range("A1:D" & lastRow) dataRange.SpecialCells(xlCellTypeVisible).Copy Destination:=dst.Range("A1") End Sub
On the source sheet, confirm that the filter covers the complete range and apply the desired criteria. Run the macro from Developer > Macros, then compare the visible source rows with the output on the destination sheet.

Work with Filtered Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for viewing and filtering spreadsheets. Open compatible Excel files to continue working with your data; check macro compatibility separately when migrating VBA workbooks.

Frequently Asked Questions
Why does COUNTA make my VBA copy range too short?
COUNTA counts nonempty cells rather than identifying the last occupied row. If the column has gaps, that count can be smaller than the last row number. Search the relevant data columns for the last occupied cell instead.
Can I find the last row using just one column?
Yes. If a column is populated in the final data row, use src.Cells(src.Rows.Count, "A").End(xlUp).Row, replacing A with your reliable column. Internal blanks do not stop this bottom-up search, but a blank in that column on the final data row will cause it to return an earlier row.
Why are some rows still missing after I fix the range?
Check the filter criteria and confirm that the AutoFilter range includes the final rows. SpecialCells(xlCellTypeVisible) copies only visible cells, so filtered-out and manually hidden rows are excluded.
Does the example copy the header row?
Yes. The example starts at A1, so it copies the header if that row is visible. Change the starting cell if your header is on a different row.




