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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,682
Messages
5,838,787
Members
430,568
Latest member
bortey

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