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.
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.
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.
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).
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.
Code Breakdown:
- First, we created a Sub procedure- Copy_Visible_CellsOnly.
- Then we selected the reference of the range and copied them by using the Range and Copy option.
- 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.
Similar Readings
- How to Copy Visible Cells Only without Header Using VBA
- Excel VBA: Select Visible Cells After Autofilter
- Excel VBA to Select First Visible Cell in Filtered Range
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.
