CODEWITHSUNDEEP
vbaexcelexcel-2010
torres
torres

Reputation: 1353

Deleting empty rows - MS Excel using VBA

I am using following VBA to delete all the rows that does not have * in Column T. It works but it only deletes 1 row at a time. I have to run the vba 25 times in order to delete 25 rows that does not have * in the column T. Do you see anything wrong with the code?

Sub DeleteCells()

    'Loop through cells A1:A10 and delete cells that contain an "x."
    For Each c In Range("t1:t35")
        If c <> "*" Then c.EntireRow.Delete
    Next

End Sub

Upvotes: 0

Views: 781

Answers (1)

sous2817
sous2817

Reputation: 3960

To delete rows you should work from the bottom up rather than the top down:

Sub DeleteCells()
Dim i As Long

For i = 35 To 1 Step -1
    If Range("T" & i).Value <> "*" Then Range("T" & i).EntireRow.delete
Next i

End Sub

A more efficient way would be to use the autofilter and avoid looping all together. Something like:

Sub DeleteCellsFilter()
    Sheet1.Range("$T$1:$T$35").AutoFilter Field:=1, Criteria1:="<>*"
    Sheet1.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.delete
    Sheet1.UsedRange.AutoFilter
End Sub

This makes a couple assumptions that you should correct:

1) We're interacting w/ Sheet1 2) Your range is static (rows 1 to 35).

You should adjust these assumptions as appropriate.

Upvotes: 4

Related Questions