How to Copy Only Visible Cells in Excel (4 Quick Ways)

Have you faced the troublesome situation of copy-pasting cells with hidden cells? Obviously, it is irritating and time-consuming. This article will demonstrate to you how to copy visible cells only in Excel in 4 Fast Ways. These ways might be helpful for Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013, Excel 2010 & Excel 2007.

How to Copy Only Visible Cells in Excel: 4 Quick Ways

First of all, get introduced to our dataset. In the following figure, we have a dataset of an institution that contains information about students, though the 7th row is missing. Our target is to copy the dataset except for that hidden row.

1. Using a Keyboard Shortcut to Copy Only Visible Cells

When you have less time to accomplish any analysis, using shortcuts may be quite helpful for you. We use CTRL+C to copy anything but it will not copy only visible cells by default in Excel. There is a useful keyboard shortcut for copying visible cells only in Excel and it is ALT + ; (semicolon). The following steps are for the keyboard shortcut:

• Select the dataset B4:D10.
• Press ALT + ; (semicolon).
• Copy the dataset (by pressing CTRL+C).

• Paste on your desired location (by pressing CTRL + V). We copied to range F4:H9.

Read More: How to Select Visible Cells in Excel

2. Using Go To Special Tool to Copy Only Visible Cells

Now we’ll learn two ways of applying the Go To Special tool to copy visible cells only.

2.1. From the Home Tab

You can copy only visible cells that are discussed using the Go To Special tool. You may proceed with the following steps:

• Choose the Find & Select option from the Editing section of the Home ribbon.
• Pick the Go To Special command from the Find & Select dropdown.

• Click the Visible cells only option.
• Press OK.

• Select the cell range B4:D10.
• Copy the cell range B4:D10 (by pressing CTRL+C).

• Paste where you prefer and the result is shown in the following figure (by pressing CTRL+V).

2.2. Shortcut Keys

There is a shortcut way in Excel to use the Go To Special tool. Necessary steps are shown sequentially:

• Select the cell range B4:D10.
• Press CTRL+G.
• Pick the Special option from Go To tool.

• Choose Visible cells only.
• Press OK.

• Select the dataset B4:D10.
• Copy by pressing simply CTRL+C of the dataset B4:D10.

• Paste where you want by pressing simply CTRL+V.

Read More: How to Paste into Visible Cells Only in Excel

3. Customizing Quick Access Toolbar to Copy Only Visible Cells

Located above the left-hand ribbon, the Quick Access Toolbar gives access to widely used commands and capabilities. You can customize the Quick Access Toolbar. In the following figure, we see a dataset of educational institutions where student ID, name, and program are shown. But if the 7th row is missing, how can you copy only visible cells that are discussed using the Quick Access Toolbar? You may the following steps:

• Open the Customize Quick Access Toolbar by clicking on the icon.
• Click on More Commands.

• Select Commands Not in the Ribbon.
• Pick Select Visible Cells.
• Click Add.
• Press OK.

• Select the cell range B4:D10.
• Pick the Select Visible Cells command from the quick access toolbar.

• Copy the cell range B4:D10 (by pressing CTRL+C).

• Paste where you want and it is the result (by pressing CTRL+V).

Read More: [Fixed!] Paste Visible Cells Only Not Working

4. Using Excel VBA to Copy Visible Cells Only

Lastly, we’ll use Excel VBA to copy visible cells only. 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, that it will only copy the values, not the formats. Now go forward with the following steps:

• Press ALT + F11 to open the VBA window.

• Next, click as follows to insert a new module: Insert > Module.

• Then type the following codes in the module-

Sub Copy_Visible_CellsOnly()
    Range("B4:D10").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

• Finally, just press the Run icon.

Now see, the cells are copied without the formats.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Conclusion

Now you have the above ways for copying only visible cells in Excel, you’ll have no trouble, and as far as I assure you. Thank you for reading this article and stay tuned for our next post. If you have any queries or opinions, please share them in the comments section below.