VBA Copy Visible Cells: How to Paste Filtered Rows in Excel

Yes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the source range, then copy that result and paste it at the destination. This copies cells visible in the selected range, so filtered-out or manually hidden rows are excluded; the example below also checks for the case where no cells are visible.

What “visible cells” means in Excel

Excel can hide rows or columns in more than one way. An AutoFilter hides rows that do not meet its criteria, and a user can also hide rows or columns manually. The VBA constant xlCellTypeVisible identifies cells that are visible rather than hidden, so it is useful for copying the displayed portion of a table without bringing along filtered-out records.

The method applies to the range you specify; it does not decide which data you meant to copy. If you specify A1:D100, visible cells in those columns and rows are candidates. A visible title or header within that range is copied too. If you want a particular set of fields, define that source range precisely rather than selecting an entire worksheet.

Visible cells can be a multi-area range. For example, if rows 3 and 6 are hidden, the visible parts of a column consist of separate pieces rather than one continuous block. That distinction matters when choosing how to paste, as discussed below.

A VBA macro to copy and paste visible cells

In this example, data is in A1:D100 on a worksheet named Data, and the result goes to A1 on a worksheet named Output. Change the sheet names and range to match the workbook. The destination should be clear of data you need to preserve, and using another sheet avoids overwriting the source.

Sub CopyVisibleCellsToOutput()
    Dim sourceRange As Range
    Dim visibleRange As Range
    Dim destinationCell As Range

    ' Set the source and destination ranges.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:D100")
    Set destinationCell = ThisWorkbook.Worksheets("Output").Range("A1")

    ' SpecialCells raises an error if no cells in the range are visible.
    On Error Resume Next
    Set visibleRange = sourceRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If visibleRange Is Nothing Then
        MsgBox "There are no visible cells to copy.", vbInformation
        Exit Sub
    End If

    ' Copy visible cells, then paste them at the destination.
    visibleRange.Copy
    destinationCell.PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
End Sub

The macro uses Excel’s clipboard so the copy operation can handle the visible portions as separate areas. Pasting at one destination cell places the copied visible content in a compact block, rather than intentionally reproducing the gaps made by hidden rows. xlPasteAll includes cell contents and formatting. If the source has formulas, the formulas are copied and may adjust their relative references at the new location.

To run it in desktop Excel for Microsoft 365 on Windows, save a backup copy first, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Return to Excel and run the macro from the Developer tab or the editor. Save a macro-enabled workbook as an .xlsm file if you need to keep the code. On Mac, Excel’s VBA editor is available in the desktop app; the shortcut can depend on the keyboard, so use the Developer tab or Excel’s Tools menu if needed. Excel for the web does not run VBA macros.

How to copy visible cells manually

For a one-time operation, there is no need to write a macro. Apply the filter or hide the rows, select the range you want, and choose visible cells only before copying. On Windows desktop Excel, press Alt+; (Alt plus semicolon) to select visible cells in the current selection. Then press Ctrl+C, click the destination, and press Ctrl+V. This shortcut does not itself apply a filter; it limits the selection to cells that are currently visible.

You can also use the Go To Special dialog: select the source range, open Home > Find & Select > Go To Special, choose Visible cells only, and confirm. The ribbon layout and shortcut availability can differ in Excel for Mac, so use the equivalent Go To Special command in the Mac desktop app if the Windows shortcut is unavailable. Once copied, paste into the desired destination. Excel for the web has different feature support and cannot run the VBA macro above.

One common surprise is that ordinary copying does not always mean “visible cells only.” Depending on the selection and operation, hidden rows can still be included. Explicitly selecting visible cells, either with the shortcut, dialog, or VBA, makes the intent clear.

Choosing the right source range and destination

Think about both range boundaries before running the macro. The example includes row 1, so if it contains column headings those headings are copied. If the source should contain only data rows, change the range accordingly, for example to A2:D100. A filter can also leave only the header visible; the macro will copy that visible header rather than treating the result as empty.

Use a bounded range when the dataset has a known size. It is easy to review and avoids copying unrelated cells.
Use a table’s data range when records are stored in an Excel table and you want the macro’s source to follow that table’s actual dimensions. Set the range deliberately, since including the header or totals row changes what is copied.
Check the destination for existing content. Pasting a block can replace cells there. The macro does not insert new rows or ask for confirmation before pasting.
Avoid an overlapping destination if the source and destination are on the same sheet. Copying into the source area can overwrite data or produce confusing results.

Also consider whether hidden columns should be omitted. Since the method selects visible cells, manually hidden columns within the source range are excluded along with hidden rows. If you want visible rows but need a column that is hidden, include it only after making it visible, or build a separate source range that explicitly includes the required fields.

Paste values, formulas, or formatting?

The sample uses xlPasteAll, which is useful when the copied block should retain its formulas and appearance. It is not always the best choice. A report intended as a fixed snapshot may need values only, while a clean export may need values without source formatting.

To paste values instead, replace the paste statement with destinationCell.PasteSpecial Paste:=xlPasteValues. This pastes calculated results rather than formulas. To paste formats only, use xlPasteFormats. These options change what is transferred, not which source cells count as visible. If you choose values, date and number display can depend on the destination’s existing number formats; use a separate format-paste step if the appearance matters.

When copied formulas refer to other cells, Excel normally adjusts relative references for the new location, just as it does during a regular copy and paste. If a report needs fixed results, values-only paste avoids carrying those formulas and their dependencies into the output.

Common errors and ways to prevent them

SpecialCells can raise a run-time error when there are no matching visible cells, which is why the example handles that call with a narrow error-suppression block and then checks whether the result was set. Keeping On Error Resume Next limited to that statement is important: leaving it active for the whole macro can hide unrelated mistakes, such as a misspelled worksheet name.

Run-time error 1004: Check that the source worksheet exists and the range address is valid. A no-visible-cells condition is handled by the example, but other invalid references still need correction.
Unexpected headings: The source range may include a header row. Adjust the range if the header should not be part of the copied result.
Unexpected fields missing: A hidden source column is not visible and will be excluded. Unhide it or revise the source layout before copying.
Unexpected layout: A source with separated visible areas is not the same as a single continuous range. Copy and paste through the clipboard as shown, rather than assuming a multi-area range behaves like one rectangle in every VBA operation.
Merged cells: Merged ranges can make a visible-cell selection or paste behave differently from a normal rectangular dataset. Avoid merged cells in data tables where possible, or test the exact range on a backup copy.

For repeatable reports, verify the number and order of output rows after a test run. Filters can change between runs, and the macro copies the currently visible state; it does not impose or preserve a particular filter criterion.

Safety, undo, and workbook compatibility

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, and this macro pastes directly into the destination without asking before replacing existing cells. Consider clearing or checking the destination range first, especially if the size of the visible result changes from one run to another.

Macros require a desktop version of Excel that supports VBA, and workbook security settings may block them. Only enable macros in files you trust. Saving as .xlsm preserves VBA code; saving as a macro-free .xlsx file does not retain the macro. If coworkers use Excel for the web, they can view workbook data but will need desktop Excel to run this VBA procedure.

Frequently Asked Questions

Does SpecialCells(xlCellTypeVisible) exclude filtered rows?
Yes. Applied AutoFilter criteria hide nonmatching rows, and those hidden rows are excluded from the visible-cell result. Manually hidden rows and columns are excluded as well.

Will the macro copy the column headings?
Only if the headings are inside the source range and visible. In the example, A1:D100 includes row 1. Start the range at row 2 if you do not want that row copied.

Does the macro paste visible cells into gaps or compactly?
The example copies the visible cells to the clipboard and pastes at one destination cell, producing a compact output of the copied visible content. The source’s hidden-row gaps are not a request to leave blank rows in the destination.

How do I paste only values?
Change the paste line to destinationCell.PasteSpecial Paste:=xlPasteValues. This transfers results rather than formulas; it does not change the source selection.

Can I use this in Excel for the web?
No. Excel for the web does not execute VBA macros. Use the web app’s ordinary copy and paste features where suitable, or open the workbook in desktop Excel to run the macro.

When a different approach makes sense

For a single filtered copy, the Visible cells only command is often the fastest option. VBA is more useful when the same export needs to be repeated, the destination is predictable, or a larger procedure must prepare and copy the data consistently. If the output should remain dynamically linked to the source rather than being a pasted snapshot, a formula-based report or a query-based workflow may be a better fit. In every case, the key decision is the same: define exactly which source columns and rows belong in the result, then make the copy method match whether you need formulas, values, and formatting.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.