Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to filter a list and delete the rest hidden or visible rows in Excel?

For a filtered list, you may need to delete the hidden or visible rows in order to keep only the useful data. In this article, we will show you methods of deleting the rest hidden or visible rows of a filtered list in Excel.

Delete hidden rows in active worksheet with VBA code

Delete visible rows of filtered list with selecting all visible cells

Delete hidden or visible rows of filtered list with Kutools for Excel


Delete hidden rows in active worksheet with VBA code


This section will show you VBA code to delete hidden rows in active sheet. Please do as follows.

1. Activate the worksheet you need to delete hidden rows, press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. And then copy and paste the below VBA code into the Module window.

VBA code: Delete hidden rows

Sub RemoveHiddenRows()
	Dim xRow As Range
	Dim xRg As Range
	Dim xRows As Range
	On Error Resume Next
	Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
	If xRows Is Nothing Then Exit Sub
		For Each xRow In xRows.Columns(1).Cells
			If xRow.EntireRow.Hidden Then
				If xRg Is Nothing Then
					Set xRg = xRow
				Else
					Set xRg = Union(xRg, xRow)
				End If
			End If
		Next
		If Not xRg Is Nothing Then
			MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
			xRg.EntireRow.Delete
		Else
			MsgBox "No hidden rows found", , "Kutools for Excel"
		End If
	End Sub

3. Press the F5 key to run the code. If there are hidden rows in active sheet, after running the code, a dialog box will pop up to tell you how many hidden rows have been deleted. Click the OK button to delete the hidden rows. See screenshot:

doc delete rest 1

Otherwise, you will get the following dialog box after running the code.

doc delete rest 1

Note: the above VBA code not only can delete hidden rows of filtered list, but also delete hidden rows which you have manually hidden before.


Delete visible rows of filtered list with selecting all visible cells feature

For deleting visible rows of filtered list, please do as follows.

1. Select all filtered out rows, and press F5 key to open the Go To dialog box, then click the Special button. See screenshot:

doc delete rest 1

2. In the Go To Special dialog box, check the Visible cells only option, and then click the OK button.

doc delete rest 1

3. Now all visible rows are selected, right click the selection, and then click Delete Rows.

doc delete rest 1

Until now, all visible rows are deleted from the filtered list.


Delete hidden or visible rows of filtered list with Kutools for Excel

The above two methods may be not the desire solutions for many Excel users, here we introduce you a handy tool. With the Delete Hidden (Visible) Rows & Columns utility of Kutools for Excel, you can easily delete hidden rows in selected range/sheets, active sheet or all worksheets in Excel.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

1. If you only want to delete hidden or visible rows of a filtered list, please select the filtered range manually, and then click Kutools > Delete > Delete Hidden (Visible) Rows & Columns. See screenshot:

2. In the Delete Hidden (Visible) Rows & Column dialog box, select In selected Range in the Look in drop-down list (you can select other options as you need), check the Rows option in the Delete type section, and in the Detailed type section, check Visible rows or Hidden rows option as you need. And finally click the OK button. See screenshot:

doc delete rest 1

3. Then a dialog box pops up to tell you how many rows have been deleted, please click the OK button.

doc delete rest 1


Delete hidden or visible rows of filtered list with Kutools for Excel

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Maximillian Eckemoff · 10 months ago
    Feedback re: the macro for hidden row deletions - this takes too long to run on 900k rows to be useful. 2+ hours on an OC'd Threadripper 1950X and still running (had to end task). Any way to optimize it to use multiple cores or is this a VBA limitation?
    • To post as a guest, your comment is unpublished.
      crystal · 10 months ago
      Hi,
      The code has been optimized. Please have a try. Thank you for your comment.

      Sub RemoveHiddenRows()
      Dim xFlag As Boolean
      Dim xStr, xTemp As String
      Dim xDiv, xMod As Long
      Dim I, xCount, xRows As Long
      Dim xRg, xCell, xDRg As Range
      Dim xArr() As String
      On Error Resume Next
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Set xRg = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
      If xRg Is Nothing Then Exit Sub
      xRows = xRg.Rows.Count
      Set xRg = xRg(1)
      xFlag = True
      xTemp = ""
      xCount = 0
      For I = 1 To xRows
      Set xCell = xRg.Offset(I - 1, 0)
      Do While xFlag
      If xCell.EntireRow.Hidden Then
      xStr = xCell.Address
      xFlag = False
      Else
      GoTo Ctn
      End If
      Loop
      If xCell.EntireRow.Hidden Then
      xTemp = xStr & "," & xCell.Address
      End If
      If Len(xTemp) > 171 Then
      xCount = xCount + 1
      ReDim Preserve xArr(1 To xCount)
      xArr(xCount) = xStr
      xStr = xCell.Address
      Else
      xStr = xTemp
      End If
      Ctn:
      Next
      xCount = xCount + 1
      ReDim Preserve xArr(1 To xCount)
      xArr(xCount) = xStr
      For I = xCount To 1 Step -1
      If I = 1 Then
      xStr = Mid(xArr(I), InStr(xArr(I), ",") + 1, Len(xArr(I)) - InStr(xArr(I), ","))
      Else
      xStr = xArr(I)
      End If
      If xDRg Is Nothing Then
      Set xDRg = Range(xStr)
      Else
      Set xDRg = Union(xDRg, Range(xStr))
      End If
      If (Len(xDRg.Address) >= 244) Or (xCount = 1) Then
      xDRg.EntireRow.Delete
      Set xDRg = Nothing
      End If
      Next
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      End Sub