VBA & Macro Problems

Select Filtered Excel Data in Columns B:G with VBA

Algirdas JasaitisAlgirdas Jasaitis Nov 20, 2026 868 views

Question details

Select and copy visible filtered data from columns B:G with VBA, excluding the first four header rows.

Select Filtered Excel Data in Columns B:G with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro needs to copy part of a filtered range without navigating from its bottom row or using End(xlDown).
Observed behavior
The source contains multiple header rows, and the macro needs to include only visible data in columns B:G.
Before you start

Make sure the source range has an AutoFilter and apply the desired filter. The example assumes rows 1–4 are headers or other information and that a worksheet named Destination exists in the active workbook.

Solution 1Recommended

Copy visible cells from the Excel AutoFilter range

Use the AutoFilter range to find filtered cells in columns B:G without depending on the current selection.

The macro intersects the AutoFilter range with columns B:G starting at row 5, then copies only visible cells. Change the starting row if your data begins elsewhere.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. Choose Insert > Module.

2
Add the macro

Paste this code into the module: Sub CopyFilteredColumns() Dim ws As Worksheet Dim dataRange As Range Dim visibleData As Range Set ws = ActiveSheet If Not ws.AutoFilterMode Then MsgBox "Apply an AutoFilter to the source range first." Exit Sub End If Set dataRange = Intersect(ws.AutoFilter.Range, ws.Range("B5:G" & ws.Rows.Count)) If dataRange Is Nothing Then MsgBox "The filter range does not include data in B:G from row 5." Exit Sub End If On Error Resume Next Set visibleData = dataRange.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If visibleData Is Nothing Then MsgBox "No visible cells were found in the data range." Exit Sub End If visibleData.Copy Destination:=ActiveWorkbook.Worksheets("Destination").Range("A1") End Sub

3
Run the macro

Return to Excel, select the filtered source worksheet, and choose Developer > Macros. Select CopyFilteredColumns and click Run.

Copy visible cells from the Excel AutoFilter range
Adjust the destination: Replace Destination and A1 in the code with your target worksheet name and starting cell. The example excludes worksheet rows 1–4; if additional header rows appear below row 4, change B5 to the first data row.
Free Microsoft Office alternative

Work with filtered spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with filters as you migrate; it does not repair Excel VBA behavior, so check macro compatibility separately.

Filter spreadsheet data through a familiar interfaceCompatible with common Microsoft Excel formats, including XLSX and XLSFree and lightweight for everyday spreadsheet workOpen existing Office files when moving to WPS Office
microsoft office alternative - wps office

Frequently Asked Questions

Why use AutoFilter.Range instead of End(xlDown)?

AutoFilter.Range identifies the range associated with the worksheet's filter. End(xlDown) navigates from a starting cell and can stop at a blank cell rather than identify the full filtered range.

How do I exclude more than four header rows?

In the macro, change B5:G to start at the first data row. For example, use ws.Range("B7:G" & ws.Rows.Count) if data begins on row 7.

What happens if the filter returns no matching rows?

If there are no visible cells in the intersected data range, SpecialCells raises an error. The example handles it and displays a message instead of copying.

Can I copy the results to a different location?

Yes. Change Worksheets("Destination").Range("A1") to the destination worksheet name and top-left cell in the active workbook.