Thread: Fill blanks in Column

Hello,

I have a column that starts with a cell containing a particular number, and every 100th cell (approximately) contains a different number. E.g, A-2 is 0.5, A-102 is 1.2, A-202 is 0.8. All the cells in-between are empty. Is there an easy way to copy/paste these values all the way down the column, changing the values once a new number is reached? E.g. the value 0.5 would be copied to cells A-3 through A-101, then 1.2 would be copied to cells 103 through 201, etc.?

Manually copy/pasting each number into the subsequent 99 cells is impractical because I have about 450 different numbers and about 45,000 rows. I'm hoping there's a way to do it all in one move? Is that possible?

BTW, if it helps, the values for each 100 cells correspond to a particular date, the values of which are entered in another column. Eg. the first 100 cells (with the value of 0.5) are 1/1/09, the next 100 cells (value of 1.2) are 1/2/09, etc.

Much thanks in advance.

Select the column.

Edit > Goto > Special, tick Blanks

In the formula bar, type =, the press the up arrow key, then press and hold the Ctrl key, then hit Enter.

Select the column again, Edit > Copy, Edit > Paste Special, Values

in b1 put =a1 in b2 put =IF(A2="",B1,A2) and drag down

Thank you both for the help, really appreciate it. Martin's solution did the trick for me.