Deleting empty rows - MS Excel using VBA

Question

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

deletion process requires to be run from last item to first. that is the reason. — Kazimierz Jawor
– Kazimierz Jawor, Commented Oct 10, 2013 at 20:12
possible duplicate of For Each Next loop unexpectedly skipping some entries — Jean-François Corbett
– Jean-François Corbett, Commented Mar 17, 2015 at 14:50

sous2817 · Accepted Answer · 2013-10-10 20:19:45Z

4

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.

edited Oct 10, 2013 at 20:19

answered Oct 10, 2013 at 20:12

sous2817

3,9602 gold badges37 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

torres Over a year ago

Works. Except I changed Range("T" & i).Value <> "*"

sous2817 Over a year ago

@torres great Glad it worked out for you. Feel free to try the Filter method, as it's a lot more efficient. Either way, if you think it's a good solid answer, please mark it as "accepted". Good luck with the rest of your project!

Siddharth Rout Over a year ago

+ 1 For Auto Filter :)

Collectives™ on Stack Overflow

Deleting empty rows - MS Excel using VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related