Macro Request: Fill in empty cells with previous Filled cell in column

A

Artis

I have a large spreadsheet that has information formatted like this:

Item Code Item Description 1 Lot Code 1 Lot Code 2
00011336 GSX RS CHEW CS 98680 200805
98798
200809

98845 200810

98902 200812
00011636 GSX RS CHEW PMT 98666 200805

98691 200806

98718 200807

98719 200807

I would like to be able to sort based on the Third and Fourth Columns,
but in order to do that I need to fill in the blank cells in Columns 1
& 2 with the data above them.

Can anyone write a macro that will fill in a blank cell on any column
selected with the data in the cell above it that is filled?

This...
http://groups.google.com/group/micr...d/96c30d4f0ec99f2/b6d7eed69fd095a7?lnk=gst&q="%3DR[-1]C"+blank+cells&rnum=9#b6d7eed69fd095a7
came close but it only is for Column D and it pastes "=R[-1]C" in the
blank cells instead of the previous filled in result.

Thanks.
Art
 
B

Bob Umlas

No macro needed. Use Edit/Goto Special, click Blanks, click OK. However that
looks, then type = and press the up arrow, and then use Ctrl/Enter and
you're done, ready to sort.
If, by chance, you also have other information on the worksheet like in cell
M3, so that the blanks selected leaves you with the active cell in row 1,
then start over & first select the entire range with the headings & on down
(leaving out M3...) Then repeat the above process.
Bob Umlas
Excel MVP
(Right from my book "This isn't Excel, it's Magic", tip #61)
 
B

bj

You probably should also select the columns you have done this with;
copy and paste special values or when you sort by a different column, you
would get changes in the cells.

Bob Umlas said:
No macro needed. Use Edit/Goto Special, click Blanks, click OK. However that
looks, then type = and press the up arrow, and then use Ctrl/Enter and
you're done, ready to sort.
If, by chance, you also have other information on the worksheet like in cell
M3, so that the blanks selected leaves you with the active cell in row 1,
then start over & first select the entire range with the headings & on down
(leaving out M3...) Then repeat the above process.
Bob Umlas
Excel MVP
(Right from my book "This isn't Excel, it's Magic", tip #61)

Artis said:
I have a large spreadsheet that has information formatted like this:

Item Code Item Description 1 Lot Code 1 Lot Code 2
00011336 GSX RS CHEW CS 98680 200805
98798
200809

98845 200810

98902 200812
00011636 GSX RS CHEW PMT 98666 200805

98691 200806

98718 200807

98719 200807

I would like to be able to sort based on the Third and Fourth Columns,
but in order to do that I need to fill in the blank cells in Columns 1
& 2 with the data above them.

Can anyone write a macro that will fill in a blank cell on any column
selected with the data in the cell above it that is filled?

This...
http://groups.google.com/group/micr...d/96c30d4f0ec99f2/b6d7eed69fd095a7?lnk=gst&q="%3DR[-1]C"+blank+cells&rnum=9#b6d7eed69fd095a7
came close but it only is for Column D and it pastes "=R[-1]C" in the
blank cells instead of the previous filled in result.

Thanks.
Art
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top