×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

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

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

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

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

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.

CODE

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.
 

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

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/
 

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

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!

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

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.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources