deleting with a for loop

[Flyingmeatball]

I have a for loop that goes through cells in Column A, and deletes them if they are 0 or blank. However, if i delete a cell, the next row is in effect skipped, because the cells shift up. How can I make the for loop run twice if it deleted a cell? I tried deleteloop = deleteloop -1, but that just hangs my excel and i have to break the code. Here is the code:

Sub clearblanks()

Dim lastopen As Integer
With Sheets("upload file")
lastopen = .Cells(.Rows.Count, "A").End(xlUp).Row - 19
End With

For deleteloop = 20 To 20 + lastopen

If Cells(deleteloop, 4).Text = "" Or Cells(deleteloop, 4).Value = 0 Then
Rows(deleteloop).Delete
End If

Next deleteloop

End Sub

 

[onlyadrafter]

Hello,

run the loop from the bottom up

e.g. FOR MY_ROWS=50 to 1 step -1

 

[texasalynn]

you need to make the loop work from the bottom up

 

[Flyingmeatball]

Never even knew that was possible. Does that mean I have to write the code in reverse too?

 

[onlyadrafter]

Hello,

no, just the loop part.

Sub clearblanks()

Dim lastopen As Integer
With Sheets("upload file")
lastopen = .Cells(.Rows.Count, "A").End(xlUp).Row - 19
End With

For deleteloop = 20 + lastopen to 20 step -1

If Cells(deleteloop, 4).Text = "" Or Cells(deleteloop, 4).Value = 0 Then
Rows(deleteloop).Delete
End If

Next deleteloop

End Sub

 

[ShaunD30]

This was helpful for me..I am so used to just looking at a spreadsheet from top to bottom that the idea of running a loop from the bottom up just never entered my mind..Saving this one for future.

Shaun

 

[Greg Truby]

Never even knew that was possible. Does that mean I have to write the code in reverse too?

Yes, you have to set your international setting to a language like Arabic that runs right-to-left and then type the code in backwards.

A couple of quick comments:

• Generally, you want to avoid dimensioning variables that will handle row position as integers; long is more robust.
• Looks like you could use autofilter here which will usually outperform a loop.

 

[Flyingmeatball]

Why avoid integers? I will never have a row position that is anything but an integer.

I'd love to hear how to use autofilter - not familiar with it at all. My visual basic knowledge is limited to what I've used and what I can parse over from my basic java skills

 

[cornflakegirl]

Integers only go up to 32,767. You can have more rows than that, so Long is more appropriate.

 

[Greg Truby]

Why avoid integers? I will never have a row position that is anything but an integer.

Emma covered this.

I'd love to hear how to use autofilter - not familiar with it at all. My visual basic knowledge is limited to what I've used and what I can parse over from my basic java skills

Try doing this by hand using Autofilter and the macro recorder. Then post back if you can't suss out how to apply what you see. I've seen enough of Audrey & Drafter's work to be quite certain that they can help you create an autofilter-using VBA solution, no prob.