how can I speed up a specific macro?

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
683
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have the code below which works fine but am looking to see if anyone knows of a way to speed it up - it currently takes around 12 secs to run.
You will notice that the ranges all have the same row numbers.
Many thanks.
Code:
Application.ScreenUpdating = False
For Each c In Range("K23:K56,AC23:AC56,AU23:AU56,BM23:BM56,CE23:CE56,CW23:CW56,EG23:EG56,EY23:EY56,FQ23:FQ56,GI23:GI56,HA23:HA56,HS23:HS56,IK23:IK56,JC23:JC56,JV23:JV56,KM23:KM56,LE23:LE56,LW23:LW56,MO23:MO56")
    Select Case Len(c)
        Case 80 To 130
            c.Font.Size = 10
        Case 131 To 150
            c.Font.Size = 9
        Case Is > 150
            c.Font.Size = 8
        Case Else
            c.Font.Size = 11
    End Select
Next
Application.ScreenUpdating = True
 
Last edited by a moderator:
Ok, seemed to have found the issue but can't understand why it created a problem...
Completely independent from the sheet I'm working in with this macro (Sheet18) and the wksheet that Sheet18's formulas are reading from, there was a picture in another wksheet that was linked to a group of cells in yet another wksheet. I've just been systematically removing elements of wksheets (tables/pics/code etc.) to see if I could identify the problem and it was a linked picture - when I deleted the picture this the code above runs in a flash - but weirdly it's not connected in any way??
The downside is that I would like to keep the picture I have deleted and it's link to the cells in another wksheet - is there maybe some code I can use that only updates the picture when I want (so it's not 'live').
Any thoughts or ideas much appreciated.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
ok, sussed it.
I have isolated the linked picture via named ranges.
Thanks again for all your help :)
 
Upvote 0

Forum statistics

Threads
1,223,627
Messages
6,173,421
Members
452,514
Latest member
cjkelly15

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
Back
Top