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.


Download Practice Workbook

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


4 Ways to Copy Only Visible Cells in Excel

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.

How to Copy Only Visible Cells in Excel


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).

Using a Keyboard Shortcut to Copy Only Visible Cells

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

Read More: How to Copy Visible Cells Only without Header Using VBA


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:

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

Using Go To Special Tool to Copy Only Visible Cells

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

Select Visible Cells Only Option to Copy Only Visible Cells

  • 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).

Read More: How to Copy and Paste in Excel and Keep Cell Size (7 Examples)


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.

Click on Special Tab to Copy Only Visible Cells

  • Chose Visible cells only.
  • Press OK.

Select Visible Cells Only Option to Copy Only Visible Cells

  • 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 Copy Alternate Rows in Excel (4 Ways)


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 their 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 Customizing Quick Access Toolbar by clicking on the icon.
  • Click on More Commands.

Customizing Quick Access Toolbar to Copy Only Visible Cells

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

Choose the Command to Copy Only Visible Cells

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

Apply Command from the Quick Access Toolbar to Copy Only Visible Cells

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

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


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, it will only copy the values, not the formats. Now go forward with the following steps:

  • Press ALT + F11 to open the VBA window.

Using Excel VBA to Copy Visible Cells Only

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

Insert New Module in Excel VBA to Copy Visible Cells Only

  • 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.

Code Breakdown:

  • First, we created a Sub procedure- Copy_Visible_CellsOnly.
  • Then selected the reference of the range and copied them by using the Range and Copy
  • Later, used the Sheets and Select command to choose the target sheet.
  • Finally, we used Range and PasteSpecial commands to paste the values in the targetted range.

Now see, the cells are copied without the formats.

Read More: How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)


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.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo