CODEWITHSUNDEEP
vbaexcel
user4242750
user4242750

Reputation: 197

Fill blank cells (Variation)

I have an issue with filling blank cells of a column.

I have 3 Column headings in A, B, C.

Under that I have variable amounts of rows, but column A and B will always have data.

Column C could have gaps. How could I do something similar to: Edit > Go To > Special > Blanks, type = in the formula bars, hit the up arrow then Ctrl+Enter

EXCEPT, with the macro only going up until the last row of A and no further.

I have:

Sub FillCellsFromAbove()
    ' Turn off screen updating to improve performance
    Application.ScreenUpdating = False
    On Error Resume Next
    ' Look in column A
    With Columns(3)
        ' For blank cells, set them to equal the cell above
        .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
        'Convert the formula to a value
        .Value = .Value
    End With
    Err.Clear
    Application.ScreenUpdating = True
End Sub

It however fills right from the bottom of the page and not from where the last "A" value is.

Upvotes: 0

Views: 470

Answers (2)

user4039065
user4039065

Reputation:

You might want to test for blanks before attempting to put formulas into cells that may not exist.

With Columns(3).Resize(Cells(Rows.Count, 1).End(xlUp).Row, 1)
    If CBool(Application.CountBlank(.Cells)) Then
        ' For blank cells, set them to equal the cell above
        .Cells.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
        'Convert the formula to a value
        .Value = .Value
    End If
End With

Upvotes: 0

John Coleman
John Coleman

Reputation: 51978

Don't use all of Column C -- first determine how far the data in Column A extends and then grab that many cells in column C:

Sub FillCellsFromAbove()
    Dim R As Range, n As Long

    n = Range("A:A").Rows.Count
    n = Cells(n, "A").End(xlUp).Row
    Set R = Range(Cells(1, 3), Cells(n, 3))

    Application.ScreenUpdating = False
    On Error Resume Next
    With R
        ' For blank cells, set them to equal the cell above
        .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
        'Convert the formula to a value
        .Value = .Value
    End With
    Err.Clear
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions