Md. Abdul Kader
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.
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 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.
- 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).
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.
- Chose 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 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.
- 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).
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.
- 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.
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
- How to Copy and Paste in Excel Without Changing the Format
- Copy a Worksheet in Excel
- [Fixed!] Paste Visible Cells Only Not Working (4 Possible Solutions)
- Copy and Paste Multiple Cells in Excel
- How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
- How to Copy the Same Value in Multiple Cells in Excel (4 Methods)
- Paste Options in Excel: A Complete Guideline
