Please Help With Easy VBA??

[Craigeeh22]

Hi
I have a quick easy question i have this code which works fine on my worksheet but i need it to look down the whole Coloum of R(i have highlighted below) at the moment it looks down the list and as soon as there is a gap it stops?? for example
ColoumR
Complete
Complete This will copy to other worksheet
Complete

Complete these wont copy due to gap????
Complete



Sub MoveRows()
Dim rngOrigin As Range, rngDest As Range
Dim i, j As Integer

i = 1: j = 1
Set rngOrigin = Sheets("Sheet1").Range("R2")
Set rngDest = Sheets("Sheet2").Range("A1").Offset(Application.WorksheetFunction.CountA(Sheets("Sheet2").Range("A:A")))

Do While rngOrigin.Offset(i, 0).Value <> ""
If rngOrigin.Offset(i, 0).Value = "Complete" Then
rngOrigin.Offset(i, 0).EntireRow.Copy
Sheets("Sheet2").Activate
rngDest.Offset(j, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Activate
rngOrigin.Offset(i, 0).EntireRow.Delete xlShiftUp
j = j + 1
i = i - 1
End If
i = i + 1
Loop
Application.CutCopyMode = False
End Sub

Thank you if you can help??

 

[xld]

Sub MoveRows()
Dim rngOrigin As Range, rngDest As Range
Dim i, j As Integer
Dim LastRow As Long

    i = 1: j = 1
    Set rngOrigin = Sheets("Sheet1").Range("R2")
    With Sheets("Sheet2")
    
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
        For i = LastRow To 2 Step -1
          
            If Sheets("Sheet1").Range("R2").Value = "Complete" Then
                Sheets("Sheet1").Rows(i).Copy .Range("A1").Offset(j, 0)
                Sheets("Sheet1").Rows(i).Delete
                j = j + 1
            End If
        Loop
    End With
    
    Application.CutCopyMode = False
End Sub

 

[Craigeeh22]

Hi i got a msg when i did your code it said:

Compile error
loop without Do?

and wont work???

can this be changed?

Thank you for quick response.

 

[Jonmo1]

I think xld must have tried a couple different methods to do the loop, and ended up with a mixture of both....

The word "Loop" should be changed to "Next"

Interesting that the error was "Loop Without Do" and Not "For without Next"

I would think logically it would have been the other way around, since it gets to the For line before it get's to the Loop line...

 

[Craigeeh22]

Thanks Jonmo but when i put next in it doesnt do anything and isnt searching the Coloum R for the word Complete??

Its not going well so far!!

 

[Jonmo1]

I didn't read the code to see what it was doing, I just saw the error...

perhaps this line

If Sheets("Sheet1").Range("R2").Value = "Complete" Then

should be changed to

If Sheets("Sheet1").Range("R" & i).Value = "Complete" Then

 

[Craigeeh22]

thanks alot that works now!!!