Delete full blank rows when some blank cells and cells with data

bpfish36

New Member
Joined
Jul 6, 2020
Messages
12
Office Version
  1. 365
I have a worksheet that has blank rows but also has rows where only some cells are blank. I want to delete the rows that are completely blank but not the rows in which any cell is used. There are 30 columns and the blank cells can be in any or multiple columns. There are over 60,000 rows so doing this manually will be a major issue. Please note the Go To Special -> Blanks method doesn't work here because it deletes ALL blanks, which scrambles my rows up. That method is apparently for sheets in which there's data in every cell of the row in the selected range. Many thanks for the help.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,049
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
How about
VBA Code:
Sub MM1()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
    If WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, 30))) = 0 Then
        Rows(r).Delete
    End If
Next r
Application.ScreenUpdating = True
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,049
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Just remember of course, it only goes across 30 columns in it's current form !.. (y) (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,352
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top