Public Sub FixData()
Dim NumRows as Long, ThisRow as Long
Dim StoredValue as String

With Worksheets("SheetName")
NumRows = .UsedRange.Rows.Count
StoredValue = ""
For ThisRow = 1 to NumRows
If .Cells(ThisRow,3)=":ON" Then
If .Cells(ThisRow,1)="" Then
.Cells(ThisRow,1) = StoredValue
Else
StoredValue = .Cells(ThisRow,1)
End If
End If
Next ThisRow
End With

End Sub

Hope I got it OK!

" wrote:

Hi,

Please find below a data extract from one of my excel sheet:
Holder Id Ref
I0009904220 00052432/C ON:
I0079752762 00049098/C ON:
00049099/C ON:
I0010853834 00049292/C ON:

Holder Id Ref
I0079752762 00051203/C ON:
00051204/C ON:
I0010853834 00051406/C ON:
I0014171215 00053957/C ON:
I0024522547 00054193/C ON:

The above is just an extract. My sheet has around 18,000 rows. My task
is to go through all those rows and basically identify those with no
value in column 1 and a value of ON: in column 3. If I find any such
row then I have to copy column 1's value with that of the row above it.
So the above would change to:
Holder Id Ref
I0009904220 00052432/C ON:
I0079752762 00049098/C ON:
I0079752762 00049099/C ON:
I0010853834 00049292/C ON:

Holder Id Ref
I0079752762 00051203/C ON:
I0079752762 00051204/C ON:
I0010853834 00051406/C ON:
I0014171215 00053957/C ON:
I0024522547 00054193/C ON:

As it's a huge file I though may be someone can provide me code for it
or suggest me how to accomplish this task quickly.

Thanks in advance.