For every cell that is blank, fill it with upper cell value

[gaclark]

Good afternoon,
I am sorting out a long table with many blank cells.
My goal is that for every blank cell in a column, it has to be filled up with the value of the upper cell.
Is it possible? I guess so but I can not solve it.
Thank you for your help.
GAC

 

[davidbeach]

What do you mean by "upper cell"? The cell above?

 

[gaclark]

Thank you for asking. Yes, exactly that, the cell above...

 

[Denial]

Logic problem? If the cell is blank it takes the closest non-blank value in the column above it. But then it is no longer blank so it no longer takes the closest non-blank value in the column above it.

If I understand your problem correctly, I had a similar need a few years ago. Suppose the column containing these blank / nonblank values is column A, and the "used" part of column A is from row 2 to row 20. You need the values in column A (actual or assumed) to be used in a formula in column B, in the same row.

My solution was to leave blank cells in column A blank, but to use a user-defined function reproduced below.

Option Base 1
Option Explicit
Function GetLastEntry(Col_of_Cells As Range) As Variant
'
'  Input is a column of cells.
'  Output is the last USED cell of the column.
'
Dim I As Long, NR As Long, NC As Long
Dim Ans As Variant
'
NR = Col_of_Cells.Rows.Count
NC = Col_of_Cells.Columns.Count
If NR < 1 Or NC <> 1 Then
    MsgBox "Input must be a single-column range.", , "Function GetLastEntry"
    GetLastEntry = CVErr(xlErrRef)
    Exit Function
End If
'
'  Search, from bottom up, for the first non-empty cell.
'  Note that the IsNull function does not do the job here.
'
For I = NR To 1 Step -1
    If Not IsEmpty(Col_of_Cells(I)) Then
        Ans = Col_of_Cells(I)
        GoTo FoundIt
    End If
Next I
Ans = CVErr(xlErrNA)
'
FoundIt:
GetLastEntry = Ans
End Function

Then, for example, in my formula in cell B9 instead of referencing A9 directly I referenced GetLastEntry($A$2:A9). Providing you get your absolute/relative addressing correct this will happily copy down the column.

When looking at the visual impact of the overall result, I convinced myself that it was actually quite intuitive to have the cell in column A blank as a signal to use the value above. I thought of it as an "automatic ditto".

Maybe something like this will work for you.

 

[IDS]

If the values to be checked are in column A, starting in row 2, then in column X (where X is any convenient letter) I'd put:

=IF(A2="",X1,A2)

copy that down to the bottom of the table, then copy the entire column and paste-special as values over column A.




Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[yakpol]

Here's the way of accomplishing this task without VBA.

1. In any cell outside of table, for example cell A2, type "=A1"
2. Copy cell A2 (righclick/copy)
3. Select range containing a table of interest and press F5 (GoTo). In the dialog box select Special/Blanks/Ok. All blank cells in the range will be selected (highlighted).
4. Paste copied formula. Rightclick/Paste special/Formulas/Ok

Should work!

 

[Denial]

We seem to be solving two different problems here. I assumed (rashly?) that the column was to be dynamic, others have assumed that it is static and so once the blanks are filled in (by the methods proposed) the column's contents will never change.

This second possibility had never occurred to me. I have now read the OP again, but still cannot be sure which approach was being sought.

 

[flash3780]

Doug's method is what I would have used... straightforward use of the =IF() command.

//signed//
Christopher K. Hubley
Mechanical Engineer
Sunpower Incorporated
Athens, Ohio