VBA – Macros

Removing Macros Q: I have an Excel file and for some reason, every time I open it, it asks me if it's OK to run macros ... but there aren't any! How do I stop this annoying message from appearing? By Jeff Robson This message means that somewhere in your file there must be [...]

Speeding up Print macros I have recorded a macro that changes print settings and then prints various sheets. The problem is that it takes a while to run. Is there a way to speed it up? By Neale Blackwood When you record macros Excel creates the VBA code for you. This is really useful [...]

Speeding up macros - calculation Stop Excel calculating By Neale Blackwood One technique to speed up macros is to turn off Excel's automatic calculation at the beginning of your macro and then turn it back on at the end of your code. If you then need the spreadsheet to calculate during your code there [...]

Creating Local Range Names in Multiple Sheets By Jeff Robson Working with grouped sheets is a great way of manipulating lots of sheets at once and eliminating repetitive tasks. Excel also lets you have the same range name on multiple sheets, each with its scope set to that sheet only. But what if you [...]

Downloading an Excel file from the web What to do when the file name changes monthly? By Jeff Robson Client problem: how to download an Excel file each month containing foreign exchange rates when the file name changes all the time. Here, you can't just record a macro to get the code you need. [...]

Protecting Sheets in Excel 2013 is Slow By Wyn Hopkins Protecting sheets takes much longer in Excel 2013 than it did in previous Excel versions. It’s not noticeable when you’re manually protecting a sheet, but if you have code that protects or unprotects sheets repeatedly, it can become a performance issue. For example, protecting [...]

Transpose Rows, Columns and Tables By Jeff Robson Transposing a Single Row or Column If you only have a single row or column of data, you can transpose this into a column or row by using the TRANSPOSE array function. For example, if your data is in cells Sheet1!A1:A5 and you want to put [...]

Speeding up macros - screen flicker Stop screen updating By Neale Blackwood One technique to speed up macros is to stop the screen from updating as the macro runs. When the screen flickers and shows each step in the macro it is actually slowing down the macro. You can insert the following command at [...]