Find Value in Column, Fill Up 'till Non-Blank Cell

0
votes

I am having trouble finding a way to use a macro to do the following:

I am finding the last populated row of Col A and offsetting one column over to paste a value in Column B. I would like to take that text that was put in Col B and fill up/copy-to all of the empty cells above it, until a non-blank cell.

Each loop inserts a different string in column B, which is why I'm hoping to find a way to paste that string up the row until it hits a non-blank cell,

For example:

A                  B
Header        Header
9/30/14
9/30/14
9/30/14
9/30/14        text1
9/30/14
9/30/14
9/30/14
9/30/14        text2

To look like:

A                  B
Header        Header
9/30/14        text1
9/30/14        text1
9/30/14        text1
9/30/14        text1
9/30/14        text2
9/30/14        text2
9/30/14        text2
9/30/14        text2

Apologies if this is terribly confusing! Any help would be greatly appreciated!

3
vbaexcel

3 Answers

1
votes

Not with a macro (though could be recorded):

Select ColumnB, Ctrl+G, Special, check Blanks, OK, =, Down, Ctrl+Enter.

1
votes

I really like pnuts' solution and would recommend that if your data is as simple as shown, this bit of VBA code should also be able to provide the desired result if you're looking for a VBA solution:

Sub tgr()

    Dim rngBlanks As Range
    Dim BlankArea As Range

    Set rngBlanks = Range("B1", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeBlanks)

    For Each BlankArea In rngBlanks.Areas
        BlankArea.Value = BlankArea.Cells(1).Offset(BlankArea.Cells.Count).Value
    Next BlankArea

End Sub
-1
votes

Try something like this, edit to suit your taste:

Sub answer()
    lastA = Range("A65000").End(xlUp).Row
    lastB = Range("B" & lastA).End(xlUp).Row + 1
    value2Paste = "Whatever U want"
    Range("B" & lastB & ":B" & lastA) = value2Paste
End Sub