VBA & Macro Problems

Fix Excel VBA AutoFilter Missing the Last Rows When Copying Data

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

The user needs an Excel VBA macro to copy all rows that match an AutoFilter, including the final rows.

Fix Excel VBA AutoFilter Missing the Last Rows When Copying Data
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook and use Developer > Visual Basic. Open the module containing the macro, or choose Insert > Module to test the example.

2
Replace the range calculation

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

3
Check the filter and run the macro

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.

Find the Last Data Row and Copy the Full Filtered Range
Existing destination data: The example copies to cell A1 on the destination sheet and can overwrite existing cells. Choose an appropriate empty destination before running it.
Free Microsoft Office alternative

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.

Filter and review spreadsheet data in WPS SpreadsheetsOpen and edit compatible Microsoft Excel formats, including XLSXUse a familiar interface for an easier transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

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.