gasrazing.blogg.se

Public Sub OptimiseWS(ByVal ws As Worksheet, ByVal opt As Boolean) Public Sub FastWS(Optional ByVal ws As Worksheet, Optional ByVal opt As Boolean = True)įor Each ws In Calculation = IIf(opt, xlCalculationManual, xlCalculationAutomatic)

Public Sub FastWB(Optional ByVal opt As Boolean = True) XlResetSettings '<- Restores all Excel settings to defaults With ThisWorkbook.Worksheets("Sheet24") 'Fully qualified worksheet If it's not corrupt, one of the first things I'd try is to disable all Excel functionality before the macro:įastWB '<- Disables all Application and Worksheet level settings If the file is corrupt, and it's feasible, try re-creating it from scratch, and run this function first

Data validation rules (Data Tab -> Data Validation -> clear all).Invalid Named Ranges (Formula Tab -> Name Manager any Refs?).database connections (check Data Tab -> Connections) (doubtful).Sheet24.Range("C22:E22").Value = "" <-SLOW HEREĪs mentioned in the comments, there might be a lot of factors contributing to this change:Įxternal links, and the external file(s) moved or deleted If you need more info I will do my best to expound if I can. Go ahead and spend few minutes to be AWESOME. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. I do this by sharing videos, tips, examples and downloads on this website. I've even restarted the computer.just in case the problem was external to Excel. My aim is to make you awesome in Excel & Power BI. There is no reason (that I can see) that this should be happening. Perhaps insufficient info, but this is all I have. I know this all sounds very vague, and I know I am giving very little to go on. I've marked the trouble areas by denoting <-SLOW HERE. I have provided the macro below, although it happens throughout the file whenever values are assigned to cells. Is this one of the things Excel files just do from time to time (like a kind of file corruption), or is there a fix? I was wondering if anyone knows what might be going on. I guess I can do that now, but in this case that would take a whole lot of work to bring that file to where I have this version. This happened to another file a while back, so I reverted to a previous version of the file and it worked fine, while the slow file continued to work slow. Right now, other files with similar macros run fine. It was lightning quick before I left yesterday, now it takes 2-3 seconds at a time to assign a value to a cell. Now Excel slows way down whenever a macro is assigning values to cells or clearing them. I came to work this morning and began work on the same file I left from yesterday.