Does anyone know if it is possible to isolate ‘ScreenUpdating = False’ to select columns only?

DLB20720

Board Regular
Joined
Sep 29, 2013
Messages
117
I have a monstrous workbook with a live data feed for stock price and other data. I use this workbook to monitor just over 5,100 stocks and depending on the data received via the live feed, Excel may have to run 100,000 calculations a second. When I first wrote all the formulas in this workbook, Excel was so overwhelmed it would lock up within seconds after opening. I’ve made quite a few changes but continue to look for ways to improve this and discovered something this morning; if I set ScreenUpdating = False, the workbook runs fine. Well, I know it is running in the background, I just can't see the changes.

All of my critical calculations are contained in 12 columns but I do not need to see them update. But I do have 6 additional columns (they retrieve some data from the 12) that I need to see and I’m trying to find code that would turn off ScreenUpdating in the 12 columns only. I would think this is possible but I’m not well versed in VBA and I can’t find any references to show a way to do this.

Does anyone know if it is possible to isolate ‘ScreenUpdating = False’ to select columns only?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Are you able to just hide the columns you need? Although I'll be honest I'm not sure if this will give you the performance increase :S

/AJ
 
Upvote 0
You know, sometimes I expect the solution to a problem to be so complex I overlook anything simple.

I hid the columns and could see an immediate improvement. The market won't kick into high gear for another 2 hours and that will be the real test but I appreciate your comment and help.
 
Upvote 0
Cool. I learnt something new there as well then about getting the improvement boost. Would be interested to know how it works under pressure :)

/AJ
 
Upvote 0
Just set it to False and then at the very end of your code set it back to True. This should give you boost improvement and all the results when the code is done.
 
Upvote 0
Adam, I'll post a result after the market opens and yes, that will be the real test. I have an Excel add-in that receives a blast of more than 80,000 stock data points and if all of them change at once (unlikely but possible) Excel would have to run roughly 100,000 additional calculations before the next stream of data arrives in roughly 0.7 seconds. There is a little market activity now but I'm guessing I am only seeing maybe 5,000 to 10,000 data points and the way my adjacent formulas are set up, they may only trigger a few thousand calculations.

Hippie, hiding the columns has made a difference. I couldn't keep this workbook open more than a few minutes without crashing but it's been running 10 to 15 minutes since I hid the columns.

I've been continuing to search on isolating columns and I found this link to an MS article that explains how to isolate every other column. I'm not great with code and am still working to get it to isolate my 8 columns. https://msdn.microsoft.com/en-us/library/office/ff193498(v=office.15).aspx

Code here;
Dim elapsedTime(2)
Application.ScreenUpdating = True
For i = 1 To 2
If i = 2 Then Application.ScreenUpdating = False
startTime = Time
Worksheets("Sheet1").Activate
For Each c In ActiveSheet.Columns
If c.Column Mod 2 = 0 Then
c.Hidden = True
End If
Next c
stopTime = Time
elapsedTime(i) = (stopTime - startTime) * 24 * 60 * 60
Next i
Application.ScreenUpdating = True
MsgBox "Elapsed time, screen updating on: " & elapsedTime(1) & _
" sec." & Chr(13) & _
"Elapsed time, screen updating off: " & elapsedTime(2) & _
" sec."
 
Upvote 0
Rat, the 12 columns pull data in and then run formulas to measure price oscillations on a tick by tick basuis. I then have additional formulas that find the extreme highs and lows and pull them into separate columns. I still need to see the 6 separate columns as that is the data I make stock trades on.
 
Upvote 0
the code you posted will hide only every 2nd Column still the screenupdating is application wide and can't be set to ranges
 
Upvote 0
Yeah, I know. Put ScreenUpdating = False at the begining of your code - thanks to that you won't be able to see the changes made after each line of code and will make the calculations quicker. Then at the very end of your code set ScreenUpdating back to True - and then all the results will be shown and you won't have to hide the columns. You can also try putting Application.Calculation = xlManual at the begining of the code and Application.Calculation = xlAutomatic at the end.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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