How to copy only visible cells in Excel?
When you set some rows and columns to be hidden in a worksheet and want to copy the only visible cells in Excel, can you just directly copy and paste it in Excel? No, there are some tricky things you should know for doing so. Please go through the following steps to know how you could only copy the visible cells in Excel.
In Excel, you can apply the Go To Special function to select the visible rows first and then copy and paste them to other location.
1. Select the visible rows that you want to copy.
2. Click Home > Find & Select > Go To Special, see screenshot:
3. In the Go To Special dialog box, select the Visible cells only option under the Select, see screenshot:
4. Then click OK button, and the visible cells will be selected only.
5. Then you can copy them and paste them to anywhere without the hidden rows or columns.
If you need to delete only the visible rows and leave the hidden rows, you can apply the above steps to select the visible rows first, after selecting the visible rows then press Delete key to remove them at once.
But, here, I can also introduce you an easy and powerful tool – Kutools for Excel, with its Delete Hidden (Visible) Rows & Columns, you can quickly delete the hidden or visible rows or columns only in a worksheet or whole workbook.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now )
1. Select the visible rows that you want to delete.
2. Then click Kutools > Delete > Delete Hidden (Visible) Rows & Columns, see screenshot:
3. In the Delete Hidden (Visible) Rows & Columns dialog box, choose In Selected Range from the Look in drop down, then select Rows in the Delete type section, and choose Visible rows under the Detailed type section, see screenshot:
4. Then click OK button, all the visible rows have been deleted immediately but leaving the hidden rows.
With this feature, you can also delete blank rows / columns and hidden rows / columns in a range / a worksheet or a workbook as you need.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 6 years agoBut this formula not applicable for merge cells. can you please share a formula applicable for merge unhide cells also.