Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My spreadsheet has the following format:

(empty)
(empty)
(empty)
(empty)
(empty)
(data)
(empty)
(empty)
(data)
(empty)
(empty)
(empty)
(data)

What is the best way to insert data into the empty cells from the data immediately below it?

For Example:

(empty)
(empty)
(empty)
(empty)
(data1)
(empty)
(empty)
(data2)

Becomes:

(data1)
(data1)
(data1)
(data1)
(data1)
(data2)
(data2)
(data2)
share|improve this question
add comment

2 Answers

Fill up from the bottom, for example column D:

Sub FillUp()
    Dim N As Long, L As Long
    N = Cells(Rows.Count, "D").End(xlUp).Row
    For L = N - 1 To 1 Step -1
        If Cells(L, "D").Value = "" Then
            Cells(L, "D").Value = Cells(L + 1, "D").Value
        End If
    Next L
End Sub
share|improve this answer
    
That's correct. As a side note: Replace .end(xlUp).ROw with a safe method –  nixda Oct 28 '13 at 21:50
    
@nixda : Thanks! I'll save your suggestion for my personal toolbox. –  Gary's Student Oct 28 '13 at 21:59
add comment

Assuming your data is in column A, and column B is empty, you can fill column B with this formula:

(Row number 1 here corresponds to the row of the cell in which you are placing this formula.)

=IF(A1="",B2,A1)

What this does is look at the cell in the current row. If that value is blank, pull the data from the cell below. The cell below, using the same lookup, will be filled with something which will then trickle up the column to all cells.

After filling in your column B with this, you can copy/paste values into the original fields in column A.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.