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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 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!
Related:

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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.
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.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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