For Each statement stopping after first loop

[Papercut]

Hello,

As the title suggests, I have a For Next loop that stops after the first loop. i am trying to copy the row of any cell that has that color in the cell. I have a work around with a do until but it takes almost 6 minutes to run. Will the For Next run faster? Also why does it stop after the first row?

lrow7 = Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Row
For Each a In Sheets("Combined").Range("C2:C" & Range("C40000").End(xlUp).Row)


    If a.Interior.ColorIndex = 40 Then
     lr = Sheets("Dupes").Cells(Rows.Count, "A").End(xlUp).Row + 1
     a.EntireRow.Copy Destination:=Sheets("Dupes").Range("A" & lr)
     a.EntireRow.Delete
    End If
Next a

Thank you

 

[Joe4]

Which sheet are you on when you call this code to run?

Note that you may have to change your "For" line to read:

For Each a In Sheets("Combined").Range("C2:C" & [COLOR=#ff0000]Sheets("Combined").[/COLOR]Range("C40000").End(xlUp).Row)

Otherwise, it will calculate the last row from whatever the ActiveSheet is at the time you are running it.

 

[Papercut]

That did it. Thank you! I thought the original call would be enough.

 

[Joe4]

You are welcome.

I thought the original call would be enough.

No, everything on the right-side of the "&" is independent of what it on the left-side of it (it is its own separate entity).
So you need to explicitly define exactly where that range is coming from, or it defaults to the ActiveSheet.