Do Until Loop Not Working for my last row !

[KNKN9]

Hi

I have the code

Sub LastRow()
Dim LastRow As Long, r As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


r = 1
Do Until r = LastRow
Sheets("sheet3").Range("A1:A43").Copy
Sheets("sheet2").Range("H1").End(xlDown).Offset(1, 0).PasteSpecial
Loop


End Sub

But this does not seem to work, the code goes on continuously and does not stop at my last row. Can someone help please.

How my data looks
Col A Col B Col C ...
x
Blank
blank
balnk
x
blank
blank
blank
x
...
Thanks in advanced,
Naomie.

 

[Gadgetgav]

Hi,

You don't appear to have anything telling Excel to increase r, try putting r = r + 1 just before the loop line.

Hi

I have the code

Sub LastRow()
Dim LastRow As Long, r As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


r = 1
Do Until r = LastRow
Sheets("sheet3").Range("A1:A43").Copy
Sheets("sheet2").Range("H1").End(xlDown).Offset(1, 0).PasteSpecial
Loop


End Sub

But this does not seem to work, the code goes on continuously and does not stop at my last row. Can someone help please.

How my data looks
Col A Col B Col C ...
x
Blank
blank
balnk
x
blank
blank
blank
x
...
Thanks in advanced,
Naomie.

 

[njimack]

You haven't actually said what you're trying to do, i.e are you simply trying to copy/paste the non-blank rows? And you've used PasteSpecial but haven't used an argument to specify what you want to paste, e.g xlValues

 

[KNKN9]

I have just tried that and still occur the same problem

 

[KNKN9]

So I have a a set range that I need to paste (doesn't matter if values or not) in a column i.e 'H' until the last row with data.

Hope that helps.

You haven't actually said what you're trying to do, i.e are you simply trying to copy/paste the non-blank rows? And you've used PasteSpecial but haven't used an argument to specify what you want to paste, e.g xlValues

 

[njimack]

OK, but why are you looping through each row when you could just copy the entire range like this:

 Sub Copy_n_Paste()
Dim LastRow As Long

Application.ScreenUpdating = False
With Sheets("sheet3")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Range(.Cells(1, 1), .Cells(LastRow, 43)).Copy Sheets("sheet2").Cells(1, 8).End(xlDown).Offset(1)
End With
Application.ScreenUpdating = True
End Sub

 

[KNKN9]

Because I need this set range repeated until the last row with data ( this last row with data will be different for each time I may run the code.

OK, but why are you looping through each row when you could just copy the entire range like this:

 Sub Copy_n_Paste()
Dim LastRow As Long

Application.ScreenUpdating = False
With Sheets("sheet3")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Range(.Cells(1, 1), .Cells(LastRow, 43)).Copy Sheets("sheet2").Cells(1, 8).End(xlDown).Offset(1)
End With
Application.ScreenUpdating = True
End Sub

 

[Scott Huish]

A couple of things I notice.
1. You never reference the row r in your loop. You would be copying the same exact range every time.
2. r is never incremented. It would never equal LastRow without an increment.
3. Why are you using a Do Loop when For Next loops will increment?

So what exactly are you trying to accomplish?

 

[Akuini]

Because I need this set range repeated until the last row with data ( this last row with data will be different for each time I may run the code.

Do you mean you want to copy-paste only non blank data from col A to col H?