For every cell that is blank, fill it with upper cell value
For every cell that is blank, fill it with upper cell value
(OP)
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
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





RE: For every cell that is blank, fill it with upper cell value
RE: For every cell that is blank, fill it with upper cell value
RE: For every cell that is blank, fill it with upper cell value
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.
CODE
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
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.
RE: For every cell that is blank, fill it with upper cell value
=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/
RE: For every cell that is blank, fill it with upper cell value
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!
RE: For every cell that is blank, fill it with upper cell value
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.
RE: For every cell that is blank, fill it with upper cell value
//signed//
Christopher K. Hubley
Mechanical Engineer
Sunpower Incorporated
Athens, Ohio