How to Copy and Paste Rows with Filtering and Hidden Columns in Excel

When working with filtered data in Excel, copying and pasting rows that include hidden columns can be tricky. By default, Excel doesn’t copy hidden columns during the copy-paste process. Luckily, there are several methods to handle this efficiently. Below are detailed steps and explanations for each method, along with corresponding VBA code for advanced users.

Using the Go To Special Feature

Method 1: Selecting Visible Cells

Select the top-left cell of the filtered range you want to copy. Press Ctrl A to select the entire filtered range. Press Alt A ; to select only the visible cells.

Copying the Selected Cells

Right-click on the selected area and choose Copy, or press Ctrl C.

Pasting into a New Location

Go to the location where you want to paste the copied data. Right-click and choose Paste, or press Ctrl V.

Unhide Columns if Necessary

If you want to include hidden columns in the pasted data, unhide them before copying. Select the columns around the hidden ones, right-click, and select Unhide.

Copying the Entire Range

Method 2: Selecting the Entire Range

If you want to copy all columns, including hidden ones, select the entire range of data including the hidden columns.

Copying the Data

Right-click and select Copy, or press Ctrl C.

Pasting into a New Location

Go to the desired location and paste using Ctrl V.

Reapply the Filter

If you need the same filter applied to the pasted data, reapply the filter after pasting.

Using VBA for Advanced Users

Method 3: VBA for Copying Rows with Hidden Columns

For users who frequently need to copy and paste filtered rows with hidden columns, VBA can provide a more streamlined solution. Below is a sample VBA macro that can be used:

pre class"language-vb"Sub CopyVisibleRowsIncludingHiddenColumns()    Dim rng As Range    Dim ws As Worksheet    Set ws  ActiveSheet    Set rng  (xlCellTypeVisible)    ' Change the destination to your desired location     Destination:ActiveSheet.Range("A1")End Sub/pre

Steps to Use VBA:

Press Alt F11 to open the VBA editor. Insert a new module - Insert - New Module. Copy and paste the code above into the module. Close the editor. To run the macro, press Alt F8, select the macro, and click Run.

Conclusion

By using one of these methods, you can effectively copy and paste rows in Excel while including hidden columns. If you frequently work with filtered data, consider creating a macro to streamline the process.