VBA & Macro Problems

Append Visible Filtered Rows to the Next Empty Excel Row with VBA

Steve KSteve K Nov 18, 2026 868 views

Question details

Append visible rows from a filtered source worksheet without overwriting existing destination data.

Append Visible Filtered Rows to the Next Empty Excel Row with VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro copies filtered data from another workbook into the active workbook.
Observed behavior
The macro calculates the destination row incorrectly and overwrites existing values.
Before you start

Identify the source and destination workbook names, worksheet names, and columns to copy. Make sure the destination key column is populated for every existing record, so it can reliably identify the last used row.

Solution 1Recommended

Qualify Worksheet References and Append Visible Rows

Use this approach when an unqualified Rows.Count or range reference may point to the wrong worksheet.

Qualify Cells, Rows.Count, and other range references with their intended worksheet. For example, wsImport.Cells(wsImport.Rows.Count, "B").End(xlUp).Row finds the last used row in column B of wsImport, regardless of the active sheet.

The example copies values from columns B:D for each visible source row. It assumes row 1 contains headers and column B is populated for every source and destination record. Hidden columns are still copied.

1
Open the VBA editor

Open both workbooks in Excel, then go to Developer > Visual Basic and select the macro's module in the Project pane.

2
Set the workbook and worksheet references

In the code below, replace Source.xlsx, Destination.xlsx, Data, and Import with your actual open workbook and worksheet names. Change B:D if your columns differ.

3
Append the visible rows

Add or adapt this macro in the module: Sub AppendVisibleRows() Dim wsSource As Worksheet, wsImport As Worksheet Dim lastSourceRow As Long, nextRow As Long, r As Long Set wsSource = Workbooks("Source.xlsx").Worksheets("Data") Set wsImport = Workbooks("Destination.xlsx").Worksheets("Import") lastSourceRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row nextRow = Application.Max(2, wsImport.Cells(wsImport.Rows.Count, "B").End(xlUp).Row + 1) For r = 2 To lastSourceRow If Not wsSource.Rows(r).Hidden Then wsImport.Range("B" & nextRow & ":D" & nextRow).Value = _ wsSource.Range("B" & r & ":D" & r).Value nextRow = nextRow + 1 End If Next r End Sub

4
Run and check the result

Apply the filter on the source sheet, then run AppendVisibleRows from Developer > Macros. Check the destination sheet to confirm that visible records were added below the existing records.

Qualify Worksheet References and Append Visible Rows
Visibility and destination scope: The macro skips rows hidden by a filter or manually. It writes values only, not formatting or formulas, and calculates the append position from destination column B.
Free Microsoft Office alternative

Work with Filtered Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for opening and editing spreadsheets in a familiar interface. It supports common Excel formats for an easier move between office suites, although it does not fix an Excel VBA scoping error and macro support can vary by edition.

Filter and review spreadsheet rows in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why should I qualify Rows.Count in my macro?

An unqualified Rows.Count can refer to the active worksheet rather than the intended destination. Use wsImport.Cells(wsImport.Rows.Count, "B").End(xlUp).Row to keep both references on wsImport.

How do I find the next empty destination row?

Find the last used row in a consistently populated destination column and add 1. For column B, use wsImport.Cells(wsImport.Rows.Count, "B").End(xlUp).Row + 1; account for headers and an empty sheet as needed.

Will the example copy rows hidden manually?

No. The check Not wsSource.Rows(r).Hidden skips both manually hidden rows and rows hidden by a filter.

Does the example exclude hidden columns?

No. It copies all values in the specified B:D range for each visible row, including values in hidden columns. Adjust the column selection if hidden columns must be excluded.

What if column B contains blanks in existing records?

Choose a destination column that is populated for every record and replace "B" in the last-row calculation. Otherwise, the macro may identify a row above existing data and overwrite it.