Macro to copy paste specific cells into empty cells below them

Hey guys,

I usually look up macros online and use them with no problems. However, this one seems a bit over the top and I can't find anything... Please bare with me while I explain what I need in detail.

I have an excel worksheet with thousands of rows. What needs to be done is that each row represents a client for my bank and I can't use V look up on this data due to merged cells (randomly across these rows). The way the data is set is clients have many deals with the bank and for each client with more than one deal their name is merged with all their deals as followed:

http://imgur.com/obT4Eam,YYa7gix#0

So what I did was find a macro to Unmerge all the cells in the worksheet which lead to the following picture and the main problem at hand:

http://imgur.com/obT4Eam,YYa7gix#1

The Clients ID Name and Number are only in the first client deal and not copied to the rest of his deals. Is there any macro to locate an empty cell and copy the cell above it to the cell then stop when reaching an already occupied cell then repeat the process for the next client? Or is this only done manually?

Thanks in advance!!!

Hi qlo_0lp, hopefully this should work for you:

Sub Fill_Blanks()
    Dim LR1 As Long ' scans down a nominated column(s) - copies the value down until next change in value until last row
    Dim szLastValue
    Dim szCell

    'Where to stop looking
    LR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Sheet1").Activate

    'Where to look
    Sheets("Sheet1").Range("A1:C" & LR1).Select 'change to whatever columns you need

    szLastValue = ""
    For Each szCell In Selection.Cells
        If szCell.Value <> vbNullString Then
            szLastValue = szCell.Value
        Else
            If szLastValue <> "" Then szCell.Value = szLastValue
        End If
    Next szCell
    
End Sub

You may want to change the LR1 variable to look at column D rather than A thinking about it.

I'm guessing as you've not been back that this solved your issue. Could you mark this solved please.