Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - 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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...