Fill non-contiguous blank cells with the value from the cell above the first blank

excel vba openoffice.org
jonestars picture jonestars · Dec 14, 2011 · Viewed 94.3k times · Source

I want to know if there is a way to do this conditional in excel or open office: 

if the cell is empty then
    the cell will have the same value as the cell above it
else
    do nothing.

Should I use a macro for this? Please help me out; I have very little experience with Excel.

c 1 | 10/21/2011
c 2 |
c 3 |
c 4 | 10/24/2011
c 5 |
c 6 |
c 7 | 10/25/2011
c 8 |
c 9 |
c10| 10/26/2011

Answer

Nikhil picture Nikhil · Jul 21, 2013

  1. Select the range that contains blank cells you need to fill.

  2. Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.

  3. Click OK, and all of the blank cells have been selected. Then input the formula “=B2” into active cell B3 without changing the selection. This cell reference can be changed as you need.

  4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells.

  5. At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to choose Copy, and then press Ctrl + Alt + V to active the Paste Special… dialog box. And select Values option from Paste, and select None option from Operation.

  6. Then click OK. And all of the formulas have been converted to values.

Related questions

How do I make vba code compatible with libre office

I have recently migrated to pclinuxos from windows and seem to like it. The only problem I am facing is that libreoffice, the default spreadsheet package is not compatible with excel macros. Below is the vba code I have: Option …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More