CODEWITHSUNDEEP
excelworksheet-function
Rod
Rod

Reputation: 15457

Fill non-contiguous blank cells with the value from the cell above the first blank

I have a column like the following:

1 red
2 blue
3 red
4 
5 blue
6
7
8 white

The blanks refer to the record above it. So #4 would be associated with red and 6 and 7 would be blue.

Is there an easy way to fill in the blanks for entire column?

Upvotes: 8

Views: 6125

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

  • Select A1:A8.
  • Press F5 to show the Goto dialog.
  • Click Special ..... Select Blanks and click OK.

That will select a noncontiguous range of blank cells.

  • Then, without selecting anything else, type =A3 and press +.
  • That will enter an array formula in all the blank cells referring to the cell above it.
  • Reselect A1:A8, and Edit - Copy.
  • Then Edit - Paste Special - Values. And you're all set.

Note that the =A3 refers to the cell above the first blank cell.

If you want to do it with a macro, you could loop through the cells and fill in the empty ones.

Public Sub FillBlanks()

    Dim rColumn As Range
    Dim rCell As Range

    If TypeName(Selection) = "Range" Then
        For Each rColumn In Selection.Columns
            For Each rCell In rColumn.Cells
                If rCell.Row > rColumn.Cells(1).Row Then
                    If IsEmpty(rCell.Value) Then
                        rCell.Value = rCell.Offset(-1).Value
                    End If
                End If
            Next rCell
        Next rColumn
    End If
End Sub

Upvotes: 16

Related Questions