Code this in VBA for Excel

[Closed]
Report
Posts
2
Registration date
Wednesday September 25, 2013
Status
Member
Last seen
October 3, 2013
-
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
-
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

Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
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.
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!