Replacing a blank with info from cell above

**Accountnt** · 04-17-2009, 01:55 PM

I have a long spreadsheet that is formatted and organized with six digit codes on the left, and then several lines of information on the right, in order to sort and compare to another report, I was trying to replace the blanks in the digits on the left.

Example:

A B C
xxxx1 xxxxxxxx
xxxxxxxx
xxxxxxxx

xxxx2 xxxxxxxx
xxxxxxxx
xxxxxxxx

I originally tried an IF function (IF A2 = "", replace with the cell above) but it gave me a circular reference error because it was in the same column.

I was hoping someone would be able to aid me with a formula or idea on how to remove the blank cells, replace them with the information from the previous cell above, and yet keep any cells that already have data in them as they are.

Edit: Ty everyone.

**VBA Noob** · 04-17-2009, 02:53 PM

Unclear from you're example if this will work for you but give it a go

http://www.contextures.com/xlDataEntry02.html

VBA Noob

**Steve R** · 04-17-2009, 02:54 PM

Can you post an example workbook without sensitive data?

**Accountnt** · 04-17-2009, 03:26 PM

VBA: Thanks, I saw that earlier, but I don't think it'll work for my problem.

I'm attaching a modified spreadsheet similar to what I'm working with, and I apologize for my lackluster example before, the site reformatted my mock spreadsheet and it looks horrible.

Rows 1-4 are what I need it to look like, and 5-15 are what the SS looks like now, so I need some way for excel to see the change in the number in Column A and adjust accordingly to fill the blanks with the new number (SS is over 10,000 lines).

**VBA Noob** · 04-17-2009, 03:30 PM

It worked for me

Select range A5 to A15 > Press F5 > Special > blanks > in the formula bar type in = A5 > hold down Ctrl then press enter. Then select the cells > copy and paste special values

VBA Noob

**Accountnt** · 04-17-2009, 04:03 PM

VBA: It comes close, but I get the "That command cannot be used on multiple selections" error when I attempt to copy/paste > values

**VBA Noob** · 04-17-2009, 04:09 PM

Select the full range not the old blank cells...or even the whole column

VBA Noob

**windknife** · 04-18-2009, 09:39 AM

Another easy way is to use a tempory column to solve it.
For example, use column k to produce data, then copy & paste it to
column A. You can see attached.

HTH,
WindKnife

**hle1983** · 06-12-2013, 01:50 PM

Originally Posted by VBA Noob

It worked for me

Select range A5 to A15 > Press F5 > Special > blanks > in the formula bar type in = A5 > hold down Ctrl then press enter. Then select the cells > copy and paste special values

VBA Noob

This is a neat little work around. It works great for me, and I can see using this function for other work around. Thanks!

**joyin** · 08-06-2013, 06:01 AM

very useful, Thanks

Replacing a blank with info from cell above

LinkBack

Thread Tools

Rate This Thread

Display

Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Re: Replacing a blank with info from cell above

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions