Why is this code so slow?

dstrickland91

New Member
Joined
Jun 12, 2015
Messages
19
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

kuldokk

New Member
Joined
Oct 16, 2014
Messages
26
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.


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

dstrickland91

New Member
Joined
Jun 12, 2015
Messages
19
I already have these at the beginning and end of all macros. Is there is no quicker solution for this task?
 

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
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

New Member
Joined
Oct 16, 2014
Messages
26
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:
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,950
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top