Code this in VBA for Excel

Closed
gchancga Posts 2 Registration date Wednesday September 25, 2013 Status Member Last seen October 3, 2013 - Oct 3, 2013 at 02:37 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Oct 7, 2013 at 10:24 AM
Can anyone code this in VBA for Excel?

If active cell is not blank, then select range from current cell plus 2 cells to the right (ie A1:C1), copy it, paste copied content downward to immediately before next non-blank cell (ie if next non-blank row is A20, then paste copied content down to A19:C19). Move to next cell, ( ie A20) and start the routine again until a blank cell is reached.

It would be much appreciated!
I used to write macros in DOS, that's old! Can't seem to learn new tricks any more!

3 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 3, 2013 at 10:43 AM
This is the same question as "build macro routine", which I just responded to.
It's still not clear when the routine should end. You say that when a blank cell is found the routine should stop, but also to paste the previously copied content.
0
Yes, it is. I just thought I put it in simple layman's term, but nothing is 'simply'.
I am sorry let me try to explain. Imagine this scene (with reference to my previous description);

Starting with a non-blank cell, say 'A1'
- copy the content from A1:C3 and paste down to range A2:C:19 (originally A2:C19 would be blank before the 'pasting' and A20 would have been non-blank because the copied content's paste destination range limit was to the last row before the next non-blank cell, which is A20). Now place cursor on A20 and start the copy/paste routine again until the last row of the file is reached, (about 20000 lines, not sure how to determine the end of the spreadsheet!).

I used hard number cell references for demonstration, but as you can see it should be dynamic or content sensitive! Let me know if you need more clarificaton, thanks.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 7, 2013 at 10:24 AM
Hi gchancga,

You say:
"not sure how to determine the end of the spreadsheet!"

And that is exactly the issue.

I already provided you with a code that copies A1:C1 and paste it down till a non-blank cell is found. And then start again with those non-blank cells.

To stop the routine I have suggested:
- Fixed range
- Stop at the (and skip the) last non-blank cell
- Stop at the cell selected before running the routine.

So the question remains: when do you want to stop the routine?
Or better yet: what determines the end of your spreadsheet?

Best regards,
Trowa
0