MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro question


Posted by Matt Schmitt on January 08, 2002 2:44 PM

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.


Posted by faster on January 08, 2002 2:59 PM

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

Posted by Tom Urtis on January 08, 2002 3:20 PM

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

Posted by Osric on January 08, 2002 3:25 PM


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

Posted by Tom Urtis on January 08, 2002 3:26 PM

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

Posted by Osric on January 08, 2002 3:31 PM

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

Posted by Tom Urtis on January 08, 2002 3:35 PM

Agreed (nt)

Posted by Joe Was on January 08, 2002 3:35 PM

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.

Posted by Osric on January 08, 2002 3:44 PM

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()

Posted by faster on January 09, 2002 8:12 AM

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

Posted by Tom Urtis on January 09, 2002 8:26 AM

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