How to Delete All Visible Rows in Excel

This post will guide you how to delete all visible rows or columns in Excel. How do I select only the visible cells in Excel 2013/2016. How to delete only visible rows without the hidden rows in Excel.

Delete Visible Rows with Go To Special Feature


Assuming that you have a list of data in range A1:C4, and you have hidden one row (Row 2) in your table, and you want to delete all visible data in your range, if you select range A1:C4, all hidden values also will be deleted. So how to only select the visible values and then delete only visible values without the hidden values in Excel. You just need to do the following steps:

#1 select the range of cells A1:C4 that you want to delete all visible values.

delete all visible rows1

#2 go to HOME tab, click Find & Select command under Editing group. And select Go To Special menu from the drop-down list. And the Go To Special dialog will open.

delete all visible rows2

#3 select Visible cells only radio button under Select section. And click OK button.

delete all visible rows3

#4 only visible cells will be selected, and if you delete those values, just press Delete key in your keyboard.

delete all visible rows4

#5 all visible values are deleted. And you can show all hidden values if they are still kept.

delete all visible rows5

delete all visible rows6

Delete Visible Rows with VBA Macro


You can also use an Excel VBA Macro to achieve the result of deleting all visible rows in your selected range of cells. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

delete all visible rows7

Sub DeleteAllVisibleRows()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to delete visible rows", "DeleteAllVisibleRows", myRange.Address, Type:=8)
    myRange.SpecialCells(xlCellTypeVisible).ClearContents
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

delete all visible rows8

#6 Select one range that you want to delete all visible rows, such as:A1:C4.

delete all visible rows9

#7 let’s see the result:

delete all visible rows6

Video: Delete Visible Rows

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar