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.

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.

Thanks