Excel macro to delete a row (excel xp)

Topic

New Reply

I wrote this macro to look in Column A and if the cell is blank (NULL) then delete the whole row. This is the only way i could figure out how to do it. It runs very SLOOOOOw. Is there a way i can make this much faster?

Public Sub xjob()
Dim rng As Range
Dim rngvalue As Variant

ScreenUpdating = False
‘lRows = Worksheets(“Sheet1”).Range(“A65536”).End(xlUp).Row

Range(“A1”).Select
For I = 1 To 400
rngvalue = Range(“A” & I).Value
If rngvalue = “” Then
Range(“A” & I).EntireRow.Delete
I = I – 1
End If
Next I

ScreenUpdating = True

End Sub

Reply | Quote

Replies

Try the following:

Public Sub DelNull()
Dim lLastRow As Long, I As Long
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
        lLastRow = .Range("A65536").End(xlUp).Row - 1
        For I = lLastRow To 0 Step -1
            If .Range("A1").Offset(I, 0).Value = "" Then
                .Range("A1").Offset(I, 0).EntireRow.Delete
            End If
        Next I
    End With
    Application.ScreenUpdating = True
End Sub

Your routine will also miss rows when there are more than one null cell in a row.

Reply | Quote

Another thing that might help includes switching off recalc at the start of the macro, then switching it back on at the end-
Application.Calculation = xlManual
‘ main routine goes here
Application.Calculation = xlAutomatic

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thank you so much! This is perfecto

Reply | Quote