Why is this code so slow?

[dstrickland91]

All,

I have a portion of code that I use in a few different modules, So I tweak the columns it applies to for each one. But when I use it, I notice it tends to add almost 3-4secs to whatever code I have it in. All it does is apply a thick bottom border to a specified range when another range changes value. Any ideas why such a simple function is so slow?

Dim rng As Range
For Each rng In Range("f:f" & lastrow)
If rng <> rng.Offset(1, 0) Then
Range("A" & rng.Row & ":G" & rng.Row).Borders(xlEdgeBottom).Weight = xlThick
End If
Next rng

 

[kuldokk]

All,

I have a portion of code that I use in a few different modules, So I tweak the columns it applies to for each one. But when I use it, I notice it tends to add almost 3-4secs to whatever code I have it in. All it does is apply a thick bottom border to a specified range when another range changes value. Any ideas why such a simple function is so slow?

Dim rng As Range
For Each rng In Range("f:f" & lastrow)
If rng <> rng.Offset(1, 0) Then
Range("A" & rng.Row & ":G" & rng.Row).Borders(xlEdgeBottom).Weight = xlThick
End If
Next rng

I think:
1. Offset may make your code slow
2. try to add this before your code, because formatting borders and updating UI may also decrease the speed of your macro.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'~~~~~> your code comes here
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[dstrickland91]

I already have these at the beginning and end of all macros. Is there is no quicker solution for this task?

 

[V_Malkoti]

Range("f:f" & lastRow)

I'm guessing that its because it runs on all 1048576 cells - lastRow variable is probably empty or else it would've returned an error. Change it to run on on a Range like Range("F1:F20000") - whatever your last row is.

 

[kuldokk]

I already have these at the beginning and end of all macros. Is there is no quicker solution for this task?

OK. I would say that you should try something like this:

[COLOR=#333333][I]
[/I][/COLOR]Dim rng as variant
Dim i as long
rng = Range("F1:F" & lastrow).value
for i = lbound(rng) to ubound(rng) -1
if rng(i,1) <> rng(i+1,1) then
' ....
end if
next i