macro to fill blank cells with location

Hi kellen

Try this

Option Explicit
Public Sub test()
    Dim LastRow As Long
    Dim Rng As Range
    Dim fCell As Range
    LastRow = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious).Row
    Set Rng = Range("A3:A" & LastRow)
    For Each fCell In Rng
        If Not fCell.Offset(0, 1) = "" And fCell = "" Then
            fCell.Value = fCell.Offset(-1, 0)
        End If
    Next fCell
End Sub

Hope this helps.

John

JB, that won't work correctly for a multi-area range.

Hi kellen
Glad it works for you. If you're satisfied, please mark your post as solved. Click the scales if you feel it appropriate.

John

Originally Posted by shg

JB, that won't work correctly for a multi-area range.

It won't? It seemed to work fine for my test which was 3 separate areas, as per the OP's example. Can you reference an example of what you mean? I used a formula in the macro that takes into account the fact the a row might be blank, it skips those...

I like this approach merely for the elimination of looping row by row, the formula does the evaluation for you all at once.

See attached.