[Excel VBA] How to Copy Only Visible Cells to Another Sheet After Filtering

2025年12月30日

Overview

There are many situations where you want to transfer rows filtered by AutoFilter directly to another sheet. If you simply execute Copy, hidden rows are also copied. However, by combining this with SpecialCells(xlCellTypeVisible), you can transfer only the visible cells.

This article explains a VBA sample that automates the process from extraction to copying visible cells and deleting the header row.

Prerequisites

Item	Content
Supported Excel	Microsoft 365 or 2016 and later
Source Data	Table starting at cell C3 on the “Data” sheet (with headers)
Destination	“Result” sheet
Macro Location	Standard Module

Sample Code (VBA)

Sub CopyVisibleRows()

    Dim srcSheet  As Worksheet        ' Source data sheet
    Dim dstSheet  As Worksheet        ' Destination sheet
    Dim filterRng As Range            ' Filter target range
    
    Set srcSheet  = Worksheets("Data")
    Set dstSheet  = Worksheets("Result")
    Set filterRng = srcSheet.Range("C3").CurrentRegion   ' Table including headers
    
    '--- Extract "Mori" in the "Person" column (3rd column) ---
    filterRng.AutoFilter _
        Field:=3, _
        Criteria1:="Mori"
    
    '--- Copy only visible cells to A1 of Result sheet ---
    filterRng.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=dstSheet.Range("A1")
    
    '--- Delete header row of destination sheet (Optional) ---
    dstSheet.Rows(1).Delete
    
    '--- Display message ---
    MsgBox "Copied extracted data to Result sheet.", vbInformation

End Sub

Key Points of the Code

Line	Explanation
9	Retrieves the entire table including headers using `CurrentRegion`. Adjust the starting cell according to your actual data.
12–14	`Field:=3` indicates the 3rd column (Person column) in the table, and `Criteria1:="Mori"` extracts the corresponding rows.
17–18	`SpecialCells(xlCellTypeVisible)` copies only the cells displayed after extraction.
21	If the header row is unnecessary, you can delete it with `Rows(1).Delete`. Skip this step if needed.

Application Examples

Requirement	Implementation Hint
Keep header row	Comment out `Rows(1).Delete`.
Extract with multiple conditions	Call `AutoFilter` multiple times or set conditions on different fields to build an AND condition.
Adjust column width after copy	Add `dstSheet.Columns.AutoFit` to automatically adjust column widths.

Frequently Asked Questions

Q: Can I use this with Tables (ListObject)?

A: Yes. You can transfer only visible cells using the same procedure by setting ListObject.DataBodyRange to filterRng.

Q: Does having many hidden rows affect copy speed?

A: Since only visible cells are copied, it is relatively fast even with large data. If performance is a concern, using Application.ScreenUpdating = False will further improve it.

Summary

By using SpecialCells(xlCellTypeVisible), you can reliably copy only the rows extracted by AutoFilter.

First, run the sample code and verify that the extraction results are correctly transferred to the Result sheet. Simply by changing the column number or extraction conditions, you can apply this to various data transfer tasks.

よかったらシェアしてね！