Delete row loop amiss

[Tigerexcel]

This macro highlights the blanks within the range but doesn't actually delete any rows and I can't work out why. There's most likely more coding here than there needs to be so happy to take any feedback on that.

Sub dellirow()
Dim rng As Range
Dim rw As Range
Dim usedrange As Range
Set rng = ActiveSheet.usedrange
rng.SpecialCells(xlCellTypeBlanks).Select
For Each rw In rng.Rows
    If WorksheetFunction.CountA(rng.EntireRow) = 0 Then
        rng.EntireRow.Delete
    End If
Next rw
End Sub

 

[steve the fish]

See if this works:

lr = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

For i = lr To 1 Step -1
    If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete Shift:=xlUp
Next

 

[Yongle]

Here is different option for you which loops through all rows up to last found used row and uses UNION to collect empty rows
All empty rows are deleted together

Sub dellirowTiger()
    Dim Del As Range, r As Long
    With ActiveSheet
        Set Del = .Cells(.Rows.Count, 1)
        For r = 1 To .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, searchDirection:=xlPrevious).Row
            If WorksheetFunction.CountA(.Rows(r)) = 0 Then Set Del = Union(Del, .Cells(r, 1))
        Next r
    End With
    Del.EntireRow.Delete
End Sub

 

[Tigerexcel]

Thanks Steve and Yongle, both versions are big improvements on mine.
Steve, you've got yours down to 3 lines which is pretty economical and it does the job. Yongle, I like the Union concept and I think I might be able to use it in another macro.
Does anyone know why my attempt failed?

 

[steve the fish]

Crucial part with yours is that it was deleting from top to bottom on the sheet. You need to delete bottom to top or excel gets confused because the rows are effectively 'disappearing'. If you delete row 10 then row 9 is still row 9 going from bottom to top. If you delete row 10 then row 11 is now row 10 going from top to bottom. Yongles code will run faster.

 

[Yongle]

If you can use a single column to determine which rows to delete (here column A), then use this one-liner

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

The above deletes EVERY row if column A does not contain a value

 

[Tigerexcel]

Crucial part with yours is that it was deleting from top to bottom on the sheet. You need to delete bottom to top or excel gets confused because the rows are effectively 'disappearing'. If you delete row 10 then row 9 is still row 9 going from bottom to top. If you delete row 10 then row 11 is now row 10 going from top to bottom. Yongles code will run faster.

Thanks Steve, I'm going to have to ponder this for a while.

 

[Tigerexcel]

If you can use a single column to determine which rows to delete (here column A), then use this one-liner

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

The above deletes EVERY row if column A does not contain a value

Well surely no one can get quicker than that, it would take coding of less than 53 characters to do it.