How do I delete blank rows?

[kin]

Hi

I have used the code

Sub DeleteBlankRows()
Application.ScreenUpdating = False
Range("A2:D19962").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

But it deletes all rows even those with data. What is wrong?

thanks

 

[kin]

I think they are blank completely

 

[Richard Schollar]

Difficult for me to suggest anything further without seeing your wb - I will send you a PM with my email address if you would like to send me a sample (with any sensitive or confidential data removed).

 

[Richard Schollar]

The only other problem I can currently think of is if you are using Excel on a Mac rather than a PC? I've come across it before where the code for filtering blanks doesn't work in Mac 2004 VBA.

 

[Legacy 14611]

Hi,
You might like to try

Sub delblankrow()
Dim n As Long, i As Long, x As Long
With ActiveSheet.UsedRange
n = .Rows.Count
For i = 1 To n
    If Not .Rows(i).Find("*") Is Nothing Then
        x = x + 1
        .Rows(i).Copy .Rows(x)
    End If
 Next i
Range(.Rows(x + 1), Rows(n)).ClearContents
End With
End Sub

Works fine on Excel 2004 for Mac. Works fine for Windows version also.

If you've got a lot of stuff you can disable screen updating by putting a line

application.screenupdating=0

after the dim line, but if not much data it shouldn't make much difference.
Also, as noted it may depend on how blank your "blank" rows really are.

 

[kin]

Excellent Ti Worked Thanks

 

[Legacy 14611]

Minor editing of that code:
The line

Range(.Rows(x + 1), Rows(n)).ClearContents

should be

If x < n then Range(.Rows(x + 1), .Rows(n)).ClearContents

although I think usually unlikely to make much difference (note the period before second "Rows").