VBA & Macro Problems

How to Copy and Paste Only Visible Cells in Filtered Excel Ranges

Natalie Taylor Oct 9, 2026 869 views

Question details

The user needs a reliable method to copy data from visible rows and paste it into corresponding visible rows in another column of a filtered range without overwriting hidden cells.

How to Copy and Paste Only Visible Cells in Filtered Excel Ranges

Product: Excel
Device & OS: not provided
Scenario: Transferring data across columns in a filtered dataset where certain rows are hidden, and maintaining exact row alignment is required.
Observed behavior: Standard copy-paste operations in Excel either paste incomplete filtered results or overwrite the data in the hidden cells.

Before you start

Ensure your Developer tab is enabled in the ribbon to access the VBA editor, and remember to save your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your code.

Solution 1Recommended

Use VBA SpecialCells to Copy and Paste Visible Rows

Creating a VBA macro ensures that hidden rows are skipped during the copy-paste operation by explicitly targeting visible areas.

This VBA method uses the xlCellTypeVisible property to isolate the areas of the range that are currently visible on the screen. By iterating through these specific areas, the macro copies them to the corresponding rows in your destination column, perfectly bypassing hidden cells and maintaining row alignment.

Open the VBA Editor

Press `Alt + F11` on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click on `Insert` and select `Module` to create a blank script window.

Add the Macro Code

Write a VBA script utilizing `Selection.SpecialCells(xlCellTypeVisible)` to loop through the visible areas of your selected range and copy their values to the target column.

Run the Macro

Close the VBA editor. Select the filtered source range you want to copy, press `Alt + F8`, choose your newly created macro, and click `Run`.

Use VBA SpecialCells to Copy and Paste Visible Rows

Macro Security Settings: If the macro fails to run, navigate to File > Options > Trust Center > Trust Center Settings > Macro Settings, and ensure macros are enabled.

Solution 2

Use 'Go To Special' for Basic Visible Copying

If you only need to copy visible cells and paste them sequentially elsewhere (without strictly skipping hidden rows in the destination), Excel's built-in tool can handle this.

Efficient Spreadsheet Management

Use WPS Spreadsheet for Seamless Macro Execution

WPS Spreadsheet fully supports VBA macros and advanced filtering functions, allowing you to run complex data manipulation tasks effortlessly. It handles visible cell selections perfectly to keep your data intact.

1. Open Your Spreadsheet: Launch WPS Office and open your filtered spreadsheet document.
2. Access the Developer Tab: Click on the Developer tab in the ribbon. If it's hidden, you can enable it in the software settings.
3. Input the VBA Code: Click on 'VBA Editor', insert a new module, and paste your SpecialCells macro script.
4. Execute the Code: Run the macro to safely copy and paste your data exclusively into the visible rows.

Full VBA macro support for automating repetitive tasksAdvanced filtering and data processing toolsHigh compatibility with Microsoft Excel (.xlsx and .xlsm formats)Lightweight, fast, and runs smoothly on older devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel sometimes paste over hidden rows in a filtered list?

By default, Excel treats a continuous destination range as a single block. If you paste data into a filtered range without using specialized macros, it inserts the data sequentially into the rows, ignoring the active filter state and overwriting hidden cells.

Is there a keyboard shortcut to select only visible cells?

Yes. You can highlight your range and press `Alt + ;` (Alt and semicolon) to instantly select only the visible cells before copying.

Can I paste data into visible cells only without using VBA?

Pasting directly into a filtered range while skipping hidden rows natively is highly restricted. While copying from visible cells is easy with shortcuts, pasting *into* a filtered list usually requires VBA for accurate row-to-row alignment.

What happens if I don't save my file as Macro-Enabled?

If you save a workbook containing VBA code as a standard `.xlsx` file, all macros will be permanently stripped and lost upon closing the file. You must always use the `.xlsm` format to retain your VBA solutions.

Excel & Data Problems