How to Copy and Paste in Excel Without Hidden Rows

If you have hidden rows from your dataset and then you want to select all cells to copy and paste them to another place, in that case, you will find them with hidden rows also. But when you want to copy and paste in Excel without hidden rows, you can follow this article. This article will show all the possible methods to copy and paste Excel without hidden rows effectively.

Table of Contents hide

Download Practice Workbook

5 Easy Methods to Copy and Paste in Excel Without Hidden Rows

1. Copy and Paste in Excel Without Hidden Rows with Go To Special Command

2. Apply Keyboard Shortcut to Copy and Paste Excluding Hidden Rows

3. Manually Select Visible Rows in Excel and Copy

4. Use Quick Access Toolbar to Copy and Paste Without Hidden Rows

5. Copy and Paste Visible Rows Through Excel VBA

Conclusion

Download Practice Workbook

Download the practice workbook below.

Copy and Paste Without Hidden Rows.xlsm

5 Easy Methods to Copy and Paste in Excel Without Hidden Rows

In order to copy and paste in Excel without hidden rows, we have found five different methods through which you can easily do the work. In this article, we would like to utilize several commands, keyboard shortcuts, and the quick access toolbar to solve the problem. However, we take a dataset that contains the salesperson, product, quantity, and total amount.

1. Copy and Paste in Excel Without Hidden Rows with Go To Special Command

Our first method is based on using the Go To Special command in Excel. Firstly, we will select the range of cells and define them as visible cells only option. After that, we can copy and paste them into a different cell. But at that time, it will take the visible rows only and ignore the hidden rows. Here, we hide rows 6, 7, and 10 respectively.

Steps

Firstly, select the range of cells B4 to E12.

Then, go to the Home tab on the ribbon.
Select Find & Select option from the Editing group.

Next, select the Go To Special option.

As a result, the Go To Special dialog box will appear.
Select Visible Cells Only.
After that, click on OK.

Copy the cell range B4:E12 by pressing CTRL+C.

Finally, select a cell where you want to paste them.
We select cell B14 and paste them by pressing CTRL+V.

2. Apply Keyboard Shortcut to Copy and Paste Excluding Hidden Rows

Our second method is based on applying the keyboard shortcut to copy and paste without the hidden rows. In this method, we will use a keyboard shortcut which helps us to select the visible cells only. After that, we will copy and paste them.

Steps

Firstly, select the range of cells B4 to E12.
Press ALT + ; (semicolon).
Then, copy the dataset by pressing CTRL+C.

Finally, select a cell where you want to paste them.
We select cell B14 and paste them by pressing CTRL+V.

3. Manually Select Visible Rows in Excel and Copy

In this section, We will show a manual way to copy rows and paste them into Excel without hidden rows. This method is not suitable for a wide range of data but you may find it useful in some cases. In my dataset, the hidden row numbers are 6,7, and 10. Now follow the steps.

Steps

Firstly, press and hold the Ctrl key on your keyboard.
Then select row numbers 5 and 8.
Later, release the Ctrl key, press and hold the Shift key, and select row number 9.
Then again press and hold the Ctrl key and select row number 11.
After that, again release the Ctrl key, press and hold the Shift key, and select row number 12.
Otherwise, select the rows one by one by pressing the Ctrl key.

Then, copy the dataset by pressing CTRL+C.

Finally, select a cell where you want to paste them.
We select cell B14 and paste them by pressing CTRL+V.

Similar Readings

4. Use Quick Access Toolbar to Copy and Paste Without Hidden Rows

Our next method is based on using the quick-access toolbar to copy and paste without hidden rows. In this method, we need to activate the command from the quick access toolbar. To have a better understanding, follow the steps.

Steps

First, click Customize Quick Access Toolbar icon.
Then click on More Commands.

A dialog box named Excel Options will open up.
Select All Commands from the Choose commands from the drop-down option.

Later, from the Command box, click on the Select Visible Cells option.
Then press Add.
Finally, press OK to apply.

As a result, you will find the Select Visible Cells command in the Quick Access Toolbar.

Now select the range of cells B4 to E12 and click the Select Visible Cells icon from the quick access toolbar.

As a result, only visible cells are now selected.
Then, click Ctrl+C to copy the content.

Finally, select a cell where you want to paste them.
We select cell B14 and paste them by pressing CTRL+V.

5. Copy and Paste Visible Rows Through Excel VBA

Lastly, we’ll use Excel VBA to copy and paste in Excel without hidden rows. A simple macro will be enough to do that. Here, we’ll copy the range to a new sheet named ‘Output’. But keep in mind, it will only copy the values, not the formats. To understand the code properly, follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

Sub Copy_Cells_Without_Hidden_Rows()
    Range("B4:E12").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Output").Select
    Range("B2").Select
    mitRows = Cells(Rows.Count, "B").End(xlUp).Row
    Range("B" & mitRows + 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("B2").Select
End Sub

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select Copy_Cells_Without_Hidden_Rows from the Macro name section.
After that, click on Run.

As a result, you will see the cells are copied without the formats.

🔎 VBA Code Explanation:

Sub Copy_Cells_Without_Hidden_Rows()

First of all, provide a name for the sub-procedure of the macro.

Range("B4:E12").SpecialCells(xlCellTypeVisible).Copy

Then, select the reference of the range and copy them by using the Range and Copy.

Sheets("Output").Select

Next, use the Sheets and Select command to choose the target sheet.

Range("B2").Select
mitRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & mitRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B2").Select

Finally, we used Range and PasteSpecial comman ds to paste the values in the targetted range.

End Sub

Finally, end the sub-procedure of the macro.

Conclusion

To copy and paste in Excel without hidden rows, we have shown five different methods through which you can easily do the job properly. All of these methods are fairly easy to understand and user-friendly. If you have any further queries, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.