Filling in the blank cells of raw data

[Kerrold]

Hi I have some raw data which is basically customer order information with the following column

Date ordered / customer name / item ordered / quantity / unit price /net value

The problem i have is that in the long list of data the date ordered and customer name only appears in the first row of that orders details so if the customer has purchased more than one item after item 1 rows a and b are empty.

I therefore need some code that will look a cell a1 and if there is an entry in that cell look at the row below and if a2 is empty and there is an entry in c2 copy a1:b1 down and repeat this until it gets to the bottom of all of the data.

Many thanks in advance, Kerrold

 

[VoG]

Try

Sub afill()
Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
With Range("A1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[Chris Mack]

Sub FillBlanks()
    n = Cells(Rows.Count, 3).End(xlUp).Row
    For r = 1 To n
        If Cells(r, 1) = "" And Cells(r, 3) <> "" Then
            Cells(r, 1) = Cells(r - 1, 1)
            Cells(r, 2) = Cells(r - 1, 2)
        End If
    Next
End Sub

Hope this helps,

Chris.

 

[snb_]

Sub M_snb()
    For Each ar In Sheet2.Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas
      ar.Value = ar.Cells(1).Offset(-1)
      ar.Offset(, 1) = ar.Cells(1).Offset(-1, 1)
    Next
End Sub

 

[Kerrold]

Thanks for your help everyone that's brilliant! Sorted now