Excel Fill blank rows with values from above

Question

Looking to fill blank values in a row with the 'top' value, similar to the functionality provided by Editing>Fill>Top. The difference being that the Fill function requires you to go row-by-row rather than applying itself to a larger dataset. Example below:

Apple   1   foo
Banana  1   foo
        2   foo
            bar
Cherry  2   bar
        3   foo
        6   bar
Grape   1   foo

Would end up as the following:

Apple   1   foo
Banana  1   foo
Banana* 2   foo
Banana* 2*  bar
Cherry  2   bar
Cherry* 3   foo
Cherry* 6   bar
Grape   1   foo       //new values represented with *

Note that the third entry in the second column (2) was also brought down implying this could be applied to multiple columns.

The value in this would be converting a table that has the relationships Cherry>2, Cherry>3, Cherry>6 represented in the first table into a format that could be used as an associative table.

It was in the recommended, and I'm going back and forth to MySQL - mistakenly added it — Tyler Dickson, Nov 16 '17 at 17:59

score 2 · Accepted Answer · answered Nov 16 '17 at 18:16

Try this short macro:

Sub FillInTheBlanks()
    Dim i As Long, N As Long, j As Long

    N = Cells(Rows.Count, "C").End(xlUp).Row

    For i = 2 To N
        For j = 1 To 2
            If Cells(i, j).Value = "" Then Cells(i, j).Value = Cells(i, j).Offset(-1, 0).Value
        Next j
    Next i
End Sub

score 0 · Answer 2 · answered Sep 27 '21 at 02:46

I thought id add my variation on this one. Works when you just want to select a range

Sub BlankCellAll()

For Each c In Worksheets("Sheet1").Range("A3:R14").Cells
 
 If c.Value = "" Then c.FormulaR1C1 = "=R[-1]C"
 
 c.Value = c.Value
 
  
 Next

End Sub

Excel Fill blank rows with values from above

2 Answers2