VBA Loop Issue

[sdrowsick]

Hello! I'm new here, so please let me know how I can best improve for future posts...

My question is about a VBA loop in a file. I am trying to create a program that takes all the cells in a range (the range is a dynamic named range, but for this example, it's 269 rows and 37 columns), checks if each cells ends in "0" and, if the cell does end in "0", it deletes that cell and shifts the cells up. This will cut down the number of cells in the range significantly, making the next part of my program easier in terms of processing.

The VBA I have is as follows (note: "all_ids_A" is the name of the range):

Sub BlankDeleter()

'Declare and set variables
Dim rng As Range
Set ws = ThisWorkbook.Sheets("Store-Item ID (A) Creation")
Set rng = Range("all_ids_A")

'For loop
For Each cell In rng
    If ValueRight(cell, 1) = 0 Then
    cell.Delete shift:=xlUp
    End If
Next cell


End Sub

The code runs, and I get a result that is not what is intended. Of the resulting cells, a plurality of them still end in "0". My initial thoughts are that the code was skipping over cells when the up-shift occurs. My other thought was that the zero in my if-statement needs to be in quotes, as it may be evaluating text against a number. I don't want to get too far down the rabbit hole, though, and I wanted to ask the experts!

Thanks, in advance, for your help.

 

[Joe4]

Welcome to the Board!

Things get very tricky when ever you are deleting cells that you are looping through. To ensure that you are not missing any cells,, you must loop through your range backwards.
(Think about it: If you have three cells, and the first two are blank, when you delete the first one, the second one moves up to the first slot, but on the second loop, you are now looking in the second slot, not the first, so the one that moved up gets missed).

If it is a single column, it is pretty easy. You just start at the bottom, and work your way up.
If it is multiple columns, you will need to work your way across, starting at the bottom row of each column, and work your way up.

 

[frenkc1]

Maybe add this before end if

cell.Offset(-1,0)

 

[Joe4]

Maybe add this before end if

cell.Offset(-1,0)

Not advised.
If you are in the first row, that will throw an error.

 

[Fluff]

@frenkc1 Along with Joe's comment if the ValueRight function returns a 0 for an empty cell, you could end up in a permanent loop.

 

[DataBlake]

Things get very tricky when ever you are deleting cells that you are looping through. To ensure that you are not missing any cells,, you must loop through your range backwards.

in regards to this type of problem is there a way you can delete the cells without shifting and then filter out the blanks as opposed to the entire row being filtered?
otherwise i would handle it by loading an array that ignores the if statement cells and then replacing the data with the array (its faster)