Macro question

from your desription you just need to copy the last
populated cell.

Sub X()
Range("A1").Select
Do While Selection.Offset(1, 0) <> ""
Selection.Offset(1, 0).Select
Loop
Selection.Copy
Selection.Offset(1, 0).Select

End Sub

faster: question please

Is there any difference between that and simply
Range("A1").End(xlDown).Copy ?

Maybe I missed something obvious with the original post.

Thanks.

Tom Urtis

This can also be written :-

Sub X()
With Range("A1").End(xlDown)
.Copy
.Offset(1, 0).Select
End With
End Sub

But don't you want to paste something somewhere?
If you want to paste the first available blank cell with the contents copied from the cell above :-

Sub X()
With Range("A1").End(xlDown)
.Copy .Offset(1, 0)
End With
End Sub

Now I see the difference

If for instance A1, A2, and A4 have visible values, but A3 has a formula such as =IF(C3="","",C3), and C3 is blank, then your solution is indeed what Matt asked for.

My code would skip over A3 and copy A4, the last actual visible value in the range, not what Matt asked for.

Sorry,

Tom Urtis Is there any difference between that and simply

But .....

.... I suppose it depends upon what Matt means by "has anything in it" and "reaches an empty cell". If for instance A1, A2, and A4 have visible values, but A3 has a formula such as =IF(C3="","",C3), and C3 is blank, then your solution is indeed what Matt asked for. My code would skip over A3 and copy A4, the last actual visible value in the range, not what Matt asked for. Sorry, Tom Urtis : Is there any difference between that and simply

Agreed (nt)

This code will copy what ever is in "A" to "B" and stop at the first EMPTY "A". JSW

Sub myL()
Test = 1
Range("A1").Select
Do While Selection <> ""
Cells(Test, 1).Select
Test = Test + 1
Selection.Copy
Selection.Offset(0, 1).Select
ActiveSheet.Paste
Loop
End Sub

This is actually part of a procedure I am trying to automate with AutoMate software. I need a macro which will start at a1, copy a1, go to a2, if a2 has anything in it, copy a2, move to a3, if a3 has anything then copy it, and so on until it reaches an empty cell.

Or .....

... avoiding a loop :-

Sub myL()
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy .Offset(0, 1)
End With
End Sub

This code will copy what ever is in "A" to "B" and stop at the first EMPTY "A". JSW Sub myL()

Re: faster: question please

Yes. Run my code, and run your code with data only
in A1, B1 is blank. You copy A65536 and crash

Good point...thank you very much. (nt)