Deleting row in excel causes rows to shift up - work around?

[Tom21]

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 time and effort,

Tom.

 

[DougP]

Not sure exacly how but did you try adding back a blank row.
Or just delteing the contents of the cells themselves instead of deleteing the whole row.


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Tom21]

Sorry, I don't think my first mail was too clear looking at it again!

I'm trying to get rid of any null rows in the data, so I'm finding the null ones then deleting them. Trouble is, if there are two null rows together, it deletes one of them, the whole thing shifts up from that point and I'm looking at the next null row, but at the end of the loop. So when the loop starts again, it moves on another row and leaves the second null row behind.....nasty little problem that I can't think of a way of getting round.....

Any ideas?

 

[MichaelRed]

Just dont't increment the row index. If it points to the same place, the (formerly) next row moves to become the (current) current row. Has the same &quot;Index&quot;.

Not sure of the syntax of Excel, but I THINK the line in BOLD us at least WHERE to make the change.

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
Row().Delete (xlShiftUp)
End If
counter = 0
Next r


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over