CODEWITHSUNDEEP
excelvba
user13834733
user13834733

Reputation:

VBA code is not working as it should work smoothly

Using this code it takes almost 30 seconds to complete work on 100 rows and 150 column my data is increasing swiftly and soon code will take more time to initiate. please guide me what is wrong with this code.

Sub Colourise()
Dim r As Long, Val As String, C As Long
Worksheets("Assets").Activate
r = 1
Val = ActiveSheet.Cells(r, 43).Value
C = 6 '6 is yellow, 8 is cyan
For r = 1 To ActiveSheet.Rows.Count
IsEmpty(ActiveSheet.Cells(r, 43).Value) Then ' column 43 is client name
Exit For
End If
If ActiveSheet.Cells(r, 43).Value <> Val Then
If C = 8 Then
C = 6
Else
C = 8
End If
End If
ActiveSheet.Range(Cells(r, 1), Cells(r, 80)).Select ' column 80 is end of data
With Selection.Interior
.ColorIndex = C
.Pattern = xlSolid
End With
Val = ActiveSheet.Cells(r, 43).Value
Next
End Sub

Upvotes: 0

Views: 92

Answers (1)

FlexYourData
FlexYourData

Reputation: 2344

The main problem here is that you are using ActiveSheet.Rows.Count, which is probably 2^20 rows (unless you have a very old version). The amount of data will not affect how long this takes to run, since you are looking at every row, empty or otherwise. It will always scan about 1M rows.

As well as referring to the specific sheet by name, you should use UsedRange instead.

i.e.

Dim ws as Worksheet
Set ws = Sheets("Your sheet")

For r = 1 To ws.UsedRange.Rows.Count
...

Upvotes: 1

Related Questions