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" wrote:

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.



"Walter" wrote:

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