Auto Fill down to next used cell

  • Thread starter Thread starter MikeD1224
  • Start date Start date
M

MikeD1224

I have some data that looks like this:

Store Account #
1 4201
4203
4204
5 4201
4203
4204

I eventually want it to look like this:

Store Account #
1 4201
1 4203
1 4204
5 4201
5 4203
5 4204

I'm hoping to avoid doing it by hand. Is there a macro I can record or
write that will accomplish this? Thanks for the help in advance.
 
Mike -

Assuming the Headers as described below are in cells A1 and B1 and the Data
begins in Cell A2, then

1.) Enter the following formula in Cell C2:

=IF(A2<>"",A2,OFFSET(C2,-1,0))

2.) Copy the formula down column C to match the last row of data in Column A

3.) Select and Copy the cells with the data in Column C

4.) Select Cell A2, right click --> Paste Special --> Select 'Values' -->
click Okay

Matt
 
You don't need a macro. One of my favorite features is "go
to/special/blanks". Try this:
• Go to A2 and select the range that will contain all the new entries. In
this case it looks like A2:A7.
• Ctl-g (or F5) brings up the Go To menu
• Click the Special button, then select Blanks and OK
• All blank cells are now selected with the focus in one of them.
• Type in: =, UpArrow (what you are doing is creating a formula with the =
and telling it to make it equal the cell above.
• Ctl-Enter - make sure you hold the Ctl key while hitting the Enter key.
This tells Excel to put whatever is being entered in EVERY cell selected.
Note that every cell that was blank now has the formula in it to have the
information in the cell above it.
• However, you may want to not have formulas, especially if you plan to sort
the data. So reselect all the cells again.
• Ctl-C to copy it all
• Alt-E|S (paste special) Values, OK and now you have everything filled in.

By the way, this will work against a whole table of data. If you had
something like:
Store Account # Sub-account#
1 4201 1
2
3
4203 1
2
4204 1
5 4201 1
2
3
4
4203 1
4204 1
2
3

You can select all the area you want to fill in for both columns A and B and
do the same technique. The only thing is you'll have to do the
copy/paste-special/values one column at a time.

Good luck with it...
 
Back
Top