CODEWITHSUNDEEP
vbaexcel
user2770097
user2770097

Reputation: 21

How to delete all blank rows

This code makes Excel non-responsive. Anyone know why that might be? 

Sub delblank()
On Error Resume Next
ActiveSheet.UsedRange.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
If Err Then
MsgBox "No blank cells"
End If
End Sub

Upvotes: 1

Views: 1709

Answers (3)

Santosh
Santosh

Reputation: 12353

Try below code

   Sub delblank()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim rng As Range
    On Error Resume Next
    Set rng = ActiveSheet.UsedRange.Range("A:A").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "No cells found"
    Else
        rng.EntireRow.Delete
    End If

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Upvotes: -1

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

The problem is that UsedRange won't accept Range("A:A") as a property because the used range in your sheet does not contain an entire column from top to bottom of the Excel sheet, i.e. from row 1 to row 1048756.

What you want instead is to refer to the first column of UsedRange: replace Range("A:A") by Columns(1) like this:

ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Now it works.

When you have a long chain of methods and properties giving you trouble like that, it's easier to break it down into its constituents in order to find the source of the error. That's what I did:

Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim sh As Worksheet

Set sh = ActiveSheet
Set r1 = sh.UsedRange
Set r2 = r1.Range("A:A") ' Aha, error occurs here! Wow, that was easy to find.
Set r3 = r1.SpecialCells(xlCellTypeBlanks)
r3.EntireRow.Delete

When the error is gone, it's fine to put the chain back together again to get rid of the clutter.

Also don't use On Error Resume Next unless you're absolutely certain that this is what you want, because it will just swallow errors and not tell you where they came from.

Upvotes: 3

Linga
Linga

Reputation: 10555

Try something like this:

Public Sub Tester()
On Error Resume Next
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Upvotes: 0

Related Questions