Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel - Populating range of unknown size

Nov 20, 2005 4:28AM PST

I'm either missing something obvious or this is a bit of a tricky one. I want to create a macro that will automatically copy the contents of the first cell all the way down the active rows in the column. ie Column D contains values in rows 1:1456 I want to enter text in E1 and then have it copy down to E1456. What I don't want to have to do is copy and paste every time. I have to append an identical detail to several spreadsheets where the size of the 'active' portion varies greatly. I know how to calculate the number of active rows but I don't know how to auto-populate. Any suggestions gratefully received

Discussion is locked

- Collapse -
Re: Excel
Nov 20, 2005 6:07AM PST

Once you know the number of cells to fill, select the top one. Use a statement like ActiveCell.value= to fill it. Then go down with a statement like ActiveCell.Offset(1,0).Select. And repeat (Loop or While) until you're ready.

This is by head. It's one of the things I'm not well in remembering, so I have to look up the details each time in the Help. But once you've got the principle, it isn't difficult at all.

Hope this helps.


Kees

- Collapse -
A multi-cell enter
Nov 22, 2005 6:16AM PST

Here is a tip that seems that many people do not know about, it may do what you want, if you can select all the cells you want identical:

Once you select the multitude of cells (and there are many ways you can do that) you just enter what you want (which you may think of as being in just that one cell, but ...) then you hold down the ''Ctrl'' key and then press the ''Enter'' key.

Bingo! all those selected cells will have the same thing entered in them! cool. Happy

- Collapse -
auto fill
Nov 22, 2005 9:55AM PST

Try double-clicking the fill handle.

- Collapse -
... and ...
Nov 22, 2005 12:11PM PST

Re: ''Try double-clicking the fill handle.''

In what condition? at what time? to do what?

Because double-clicking the fill handle, for me, when I had one cell filled and a range selected, does nothing special.

I Googled the double-clicking of the fill handle and found Fill Handle (see para. 7), but that says you can have cells fill with identical formulas all down until the first blank adjacent cell. But that is not necessarily going to be the condition that is going to be in scrawl's spreadsheet.

So unless I'm missing something (very likely) I don't know why you are telling me that. Sorry.

I would appreciate some clarification.