Loop Skips Data - Why?

[Walter]

Here is my coding and when it goes through the loop it makes it through the
entire list of numbers but always misses the last zero which is what I am
trying to delete. Any ideas as to why this is happening? The msgbox is in
there just so I could see what was going on in my code.

Sub CopyTransferData()
'
' CopyTransferData Macro
' Copy totals and eliminate zeros, move to Data tab.
'
Dim rngCurrent As Range
Dim rngCell As Range
Dim result As Integer
Dim shtRorkERP As Worksheet
Set shtRorkERP = Application.ActiveWorkbook.Worksheets("Rork_ERP")
'
'
Range("FirstIteration").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Set rngCurrent = shtRorkERP.Range("C1").CurrentRegion
Set rngCurrent = rngCurrent.Offset(rowoffset:=0, columnoffset:=1)
Set rngCurrent = rngCurrent.Resize(columnsize:=1)

For Each rngCell In rngCurrent
result = rngCell.Value
If rngCell.Value = 0 Then
rngCell.EntireRow.Select
rngCell.EntireRow.Delete
End If
MsgBox result
Next rngCell

End Sub

 

[Don Guillett]

Seems a bit elaborate when this simple will do. When deleting rows I go from
the bottom up.

Sub delrowsifzero()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = 0 Then Rows(i).Delete
Next i

 

[Barb Reinhardt]

You need to do something like this for the delete

Dim myRange as range
Set myRange = nothing

For Each rngCell In rngCurrent
result = rngCell.Value
If rngCell.Value = 0 Then
if myRange is nothing then
Set myRange = rngCell
else
Set myRange = union(myRange,rngCell)
end if
MsgBox result
Next rngCell

if not myRange is nothing then
myRange.entirerow.delete
end if

 

[JLGWhiz]

Hi Walter, just to footnote Don's remarks, when deleting entire rows, the
code should start at the bottom of the used range to avoid skipping rows.

The default shift after deleting a row is up. So if you have two
consecutive rows that meet the delete criteria and you are progressing from
top to bottom, it will miss that second row because after the delete, the
second row is now the row with focus and the "Next" operator will select what
was the third row, thereby skipping the second row which also contained the
delete criteria. Even if you are using the Offset(1, 0) method in a
Do...Loop, the effect is the same as it works downward. But when you work
upward, the focus remains on the row beneath the next row to be evaluated.
That is why Don's code works from the higher row number to the lower with
Step -1.

 

[Barb Reinhardt]

That's why I put the deleted cells in a new range and then delete them when
I'm done (if there's anything to delete)

Barb Reinhardt

 

[JLGWhiz]

Hi Barb, I was just answering the OPs question of why it skips.

 

[Walter]

Thank you for your explanation of why it skips. I will remember from now on
to delete from the bottom up. I am learning VBA and it is great to have this
discussion board. By the way, if my range were in a place where I had to
delete cells rather than an entire row, how would I do so:

ColC ColD
Name1 100
Name2 365
Name3 0
Name4 10
Name5 0
Name6 0
Name7 55

I moved my data to the first rows on my spreadsheet because I did not know
how to delete just cells rather than the entire row. So if I wanted to
delete both the name and cell where the cell was = 0, what is the command to
do so which would fit into my code shown below?

 

[Don Guillett]

You can also use Barb's method. May be better but these days with very fast
computers, probably no difference.