Delete blank row using For Next

Question

I have a problem with my code about delete blank rows. It just has to delete some rows not all blank rows and rows value "0". I don't wanna use .SpecialCells(xlCellTypeBlanks) as some threat on SO forum.

Dim R As Integer
R = Range("CuoiNKC").Row - 1
Dim DelCell As Range
Dim DelRange As Range
Set DelRange = Range("J9:J" & R)
For Each DelCell In DelRange
    If DelCell.Value = "0" Or DelCell.Formula = Space(0) Then
        DelCell.EntireRow.Delete
    End If
Next DelCel

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:51
Related: http://stackoverflow.com/questions/10725068/some-items-get-skipped-when-looping-through-outlook-mailbox — Jean-François Corbett, Mar 17 '15 at 14:53

L42 · Accepted Answer · 2015-01-19T08:02:33.103

2

Why don't you use Range AutoFilter Method instead of looping.
Assuming you have the correct value of DelRange in your code, try this:

DelRange.AutoFilter 1, AutoFilter 1, "=0", xlOr, "=" 'filtering 0 and space
DelRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete xlUp 'delete visible cells
ActiveSheet.AutoFilterMode = False 'remove auto filter mode

Btw, if you want to stick with your logic, you need to iterate the rows backward.
You can only do that using the conventional For Next Loop. Again assuming value of R is correct.

For i = R To 9 Step -1
    If Range("J" & i).Value = "0" Or Range("J" & i).Value = " " Then
        Range("J" & i).EntireRow.Delete xlUp
    End If
Next

edited Jan 19 '15 at 08:02

answered Jan 19 '15 at 07:41

L42

18,912
11
41
65

This way as we record macro, I know this way too. But in my code I dont know why It's just delete some rows and do it again it continues delete much more but not all. With my code I want to solve more problem @L42 – kobebryant Jan 19 '15 at 08:03
Thank you L42 and if I want to know how many row was deleted, how to do? (how many i was used) – kobebryant Jan 19 '15 at 08:16
@kobebryant Just add another counter variable inside the loop. – L42 Jan 19 '15 at 10:01
how to add it. Thank you – kobebryant Jan 19 '15 at 10:12

Delete blank row using For Next

1 Answers1