Excel VBA - Code working but really, really slowly

[justme101]

Hi Guys,

I have been an admirer of this forum since a long time and it has helped me a quite a lot but this time i had to post my query as i was getting nowhere through Google searches.

So, this table below is an example of my data set:

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64, align: center"]Obj1[/TD]
[TD="class: xl66, width: 64, align: center"]obj2[/TD]
[TD="class: xl66, width: 64, align: center"]obj3[/TD]
[TD="class: xl66, width: 64, align: center"]obj4[/TD]
[TD="class: xl66, width: 64, align: center"]obj5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]58894[/TD]
[TD="class: xl65, align: center"]123[/TD]
[TD="class: xl65, align: center"]54[/TD]
[TD="class: xl65, align: center"]222[/TD]
[TD="class: xl65, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]5667489[/TD]
[TD="class: xl65, align: center"]3345[/TD]
[TD="class: xl65, align: center"]123[/TD]
[TD="class: xl65, align: center"]1113[/TD]
[TD="class: xl65, align: center"]32[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]534332[/TD]
[TD="class: xl65, align: center"]23[/TD]
[TD="class: xl65, align: center"]1123[/TD]
[TD="class: xl65, align: center"]3[/TD]
[TD="class: xl65, align: center"]414[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]788890[/TD]
[TD="class: xl65, align: center"]500300[/TD]
[TD="class: xl65, align: center"]3234[/TD]
[TD="class: xl65, align: center"]31[/TD]
[TD="class: xl65, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]716162[/TD]
[TD="class: xl65, align: center"]442[/TD]
[TD="class: xl65, align: center"]234234[/TD]
[TD="class: xl65, align: center"]2[/TD]
[TD="class: xl65, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]7234234[/TD]
[TD="class: xl65, align: center"]354[/TD]
[TD="class: xl65, align: center"]124[/TD]
[TD="class: xl65, align: center"]21324[/TD]
[TD="class: xl65, align: center"]23[/TD]
[/TR]
</tbody>[/TABLE]


There are 5 columns. What i need to do is check the first column (obj1) for values starting with "7". For all the ones that start with 7, i need to cut and paste data from adjacent cells to that cell. For e.g. the code runs and comes to row 4 (in above table) and finds that the value starts with 7 (788890). It CUTS the data from obj2 through obj5 and pastes it in column obj1 row4 itself. So, the data in row4 now becomes:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl66, width: 64, align: center"]Obj1[/TD]
[TD="class: xl66, width: 64, align: center"]obj2[/TD]
[TD="class: xl66, width: 64, align: center"]obj3[/TD]
[TD="class: xl66, width: 64, align: center"]obj4[/TD]
[TD="class: xl66, width: 64, align: center"]obj5[/TD]
[/TR]
</tbody>[/TABLE]

500300 3234 31

I did a lot of searching on google, as i am only an accountant and an enthusiast in the field of excel automation through macros/vba, and came up with this: (Line 2 in the code belows counts the used rows in Column O/Obj1 as it is the one containing the data in my file)

Sub formatdata()
1. Dim cel as Range, i as Long, j as Long
2. j = Cells(Rows.Count, "O").End(xlUp).Row
3. If (Left(Cells(i, "O").Value, 1)="7" Then
4. Cells(i, "O").Offset(,1).Resize(,5).Copy
5. Cells(i, "O").PasteSpecial xlPasteValues
6. Else
7. End If
8. Next i
End sub

This code seems to be working, but for each line to check and copy it takes about 2 seconds and i have the data in more than 36000 rows, which can go upto 70000 during critical reporting periods and waiting for so long would fail the purpose of writing a code for this.

Please help me to get a solution to speed this up or write this more efficiently. Thank you, in anticipation.

 

[justme101]

Thank you everyone. The codes are working fine. And even if it takes a second more to do the entire task, it is definitely better than taking 1 second for processing each line. Looking forward to interacting with you guys again.

and just FYI @Fluff I am a "Him" :D

 

[Fluff]

Glad we could help & thanks for the feedback