Deleting Excel row causes shift up....need a work around 1

[Guest_imported]

When I delete an excel row in a for loop the row gets deleted but the row under it is then shifted up to compensate. This means that on the next loop around I'm looking at what is essentially two rows ahead originally.....

What I need is something that lets me go back one in the loop to get the shifted up row and not skip over it. Anybody think of a work around for that?

Here's the current For loops:

counter = 0

For Each r In ws.UsedRange.Rows
For Each r2 In r.Columns
' Check each cell for a value.
If r2.Cells(1, 1).Value <> &quot;&quot; Then
counter = 1
End If
Next r2
If counter = 0 Then
r.Rows.Delete (xlShiftUp)
End If
counter = 0
Next r

Thanks for any help and time,

Tom.

 

[DarkSun]

The problem you have is when deleting rows you need to loop backwards or subtract one from the loop value.

I don't think that is possible using a For Each Loop. (I maybe wrong)

I tend to use a For Next Loop. This means you will have to obtain the Starting Row number and Max Rows from the UsedRange Object.

Then use.

For i = MaxRows To FirstRow Step -1

Next i

 

[Tom21]

Genius I tell, genius. Been using the stuff for about two weeks now, so the message was a great help to me. Sorted now.

Cheers Pal.

 

[jonscott8]

I agree with Darksun, its best to decrement the counter.

But in this case, you could get a handle to the entire range, then delete all, ISO, each range individually:

Dim oRange As Excel.Range

For Each R In ws.UsedRange.Rows
For Each r2 In R.Columns
' Check each cell for a value.
If r2.Cells(1, 1).Value <> &quot;&quot; Then
counter = 1
Exit For & why continue checking?
End If
Next r2
If counter = 0 Then
If oRange Is Nothing Then
Set oRange = R
Else
Set oRange = Union(oRange, R)
End If
End If
counter = 0
Next R
oRange.Delete xlShiftUp Jon Hawkins

 

[Kaah]

Guys,

or you might do it all in one foul swoop by using the following command:

myRange.cells.specialcells(xlcelltypeblanks).entirerow.delete

hehe sorry 'bout it!

Kaah.

BTW: if you really need a count of how many rows are deleted, it is a little harder, you'll ave to loop through areas and count rows in each, but if you restrict the object range to one column, then a count of blank cells will give you the number or rows like so:

rMyTestColumn.cells.specialcells(xlcelltypeblanks).count


let me know if you need code for counting through areas and rows in each..

k.