Select Filtered Excel Data in Columns B:G with VBA
Question details
Select and copy visible filtered data from columns B:G with VBA, excluding the first four header rows.

- 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.
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.
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.
In Excel, open the workbook and select Developer > Visual Basic. Choose Insert > Module.
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
Return to Excel, select the filtered source worksheet, and choose Developer > Macros. Select CopyFilteredColumns and click Run.

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.

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.




