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, Oct 10 '13 at 20:12
possible duplicate of [For Each Next loop unexpectedly skipping some entries](http://stackoverflow.com/questions/24515692/for-each-next-loop-unexpectedly-skipping-some-entries) — Jean-François Corbett, Mar 17 '15 at 14:50
Related: http://stackoverflow.com/questions/10725068/some-items-get-skipped-when-looping-through-outlook-mailbox — Jean-François Corbett, Mar 17 '15 at 14:52

sous2817 · Accepted Answer · 2013-10-10T20:19:45.853

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 '13 at 20:19

answered Oct 10 '13 at 20:12

sous2817

3,915
2
33
34

Works. Except I changed Range("T" & i).Value <> "*" – torres Oct 10 '13 at 20:15
@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! – sous2817 Oct 10 '13 at 20:20
+ 1 For Auto Filter :) – Siddharth Rout Oct 10 '13 at 20:40

Deleting empty rows - MS Excel using VBA

1 Answers1