How to only delete visible rows or columns in Excel？
In Excel, we usually hide some important rows or columns of information, sometimes, we need to delete the visible rows or columns only, if you delete them with Delete key directly, the hidden values will be deleted at the same time. How do you delete the visible rows or columns without deleting the hidden rows or columns in Excel? This tutorial will introduce the ways to delete visible rows only.
Delete visible values only with Go To Special command
Delete visible values only with VBA code
Delete visible rows only with Kutools for Excel
Download sample to try the methods
Comparison of Methods for Deleting Visible Rows or Columns in Excel
When a worksheet contains hidden rows or columns, deleting only the visible data requires extra care to avoid affecting hidden information. Excel's Go To Special command and VBA can clear the contents of visible cells while preserving hidden values, while Kutools for Excel can directly delete entire visible rows or columns.
| Comparison | Go To Special | VBA | Kutools Delete Hidden (Visible) Rows & Columns |
|---|---|---|---|
| What is deleted | Clears the contents of visible cells; the rows or columns themselves remain | Clears the contents of visible cells; the rows or columns themselves remain | Deletes entire visible rows or columns |
| Rows & columns | Can clear visible values in rows or columns | The provided code clears all visible cells within the selected range | Choose Rows or Columns, then specify Visible rows or Visible columns |
| Scope | Works within the range you manually select | Works within the range selected when the macro runs | Apply to a selected range, active sheet, selected sheets, or the whole workbook |
| Best for | Quickly clearing visible cell contents with Excel's built-in tools | Automating the removal of visible cell contents when VBA is acceptable | Batch deleting entire visible rows or columns while preserving hidden rows or columns |
Delete visible values only with Go To Special command
With this Go To Special command in Excel, you can quickly select the visible values and then delete them with Delete key. Do as this:
1. Select the range that you want to delete the visible data.
2. Click Home > Find & Select > Go To Special, see screenshot:
3. In the Go To Special dialog box, select Visible cells only, see screenshot:
4. Then click OK, all the visible rows have been selected in the selected range. Then press Delete key on the keyboard, the selected visible values will be deleted and the hidden values will be kept. See screenshots:
Note:
In this way, you can also remove the visible column values.
Batch delete blank/visible/hidden rows or columns in Excel |
You can delete visible, hidden or blank rows in Excel by selecting them one by one then press Delete key to remove them, but if there are hundreds of rows needed to be removed, the Delete Hidden (Visible) Rows & Columns in Kutools for Excel will do you a favor. With this tool, you can remove blank rows (columns), visible rows (columns), or hidden rows (columns) in selected range, active sheet, selected sheets or whole workbook. Click here for 30-day free trial of this tool. |
Delete visible values only with VBA code
The following VBA code also can help you to remove the visible cell values only in Excel.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
3. Then press F5 key or to run the code, and the visible cell values have been removed with the hidden values remaining.
4. Then a dialog will be displayed for you to select a range to deletd the visible cells only .
Sub DeleteVisibleRows()
'Update 20130906
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
WorkRng.SpecialCells(xlCellTypeVisible).ClearContents
Application.ScreenUpdating = True
End Sub Delete visible rows only with Kutools for Excel
The above two methods only can remove the visible values, but not the entire rows, if you have the Kutools for Excel, its Delete Rows & Columns function can help you quickly delete the visible entire rows or columns.
After free installing Kutools for Excel, please do as below:
1. Select the range that you want to apply this tool. Click Kutools > Delete > Delete Hidden (Visible) Rows & Columns, see screenshot:
3. In the Delete Rows & Columns dialog box, choose In Select Range from the Look in, then specify the Rows and Visible rows from the Delete type and Detailed type separately. Click Ok, and the prompt box will remind you how many visible rows have been deleted, click OK to close it. See screenshot:
1. You can delete the visible rows or columns from the selected range, selected sheets, active sheet or all sheets under the Look in option.
2. With this function, you also can only delete hidden rows or columns, blank rows or columns.
Conclusion
Go To Special is a convenient built-in Excel method when you only need to remove the contents of visible cells. After selecting Visible cells only, pressing Delete clears the selected visible values while keeping hidden values unchanged. However, it does not remove the entire visible rows or columns.
VBA can automate the same type of operation. The code shown in the article uses SpecialCells(xlCellTypeVisible).ClearContents to clear visible cell contents within a selected range while preserving hidden values. However, it still does not delete the actual rows or columns.
Kutools for Excel's Delete Hidden (Visible) Rows & Columns is designed for cases where the entire visible rows or columns need to be removed. You can specify whether to delete rows or columns, choose visible, hidden, or blank items, and apply the operation to a selected range, active sheet, selected sheets, or the whole workbook. This makes it particularly useful when you need to batch-delete worksheet structures rather than simply clear their contents.
Related articles:
How to remove blank or empty rows in Excel?
How to delete all hidden rows or columns in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in