Before I start, let me share a little secret with you… I really dislike VBA arrays. There just seem to be too many oddities in how they work. Compared with other programming languages, VBA seems to make arrays overly complicated. If you feel the same, then you’re in the right place. This post contains a Read More
Category: VBA Code Snippet
VBA to protect and unprotect Sheets
Protecting and unprotecting sheets is a common action for an Excel user. There is nothing worse than when somebody, who doesn’t know what they’re doing, over types essential formulas and cell values. It’s even worse when that person happens to be ourselves; all it takes is one accidental keypress, and suddenly the entire worksheet is Read More
Swap ranges, rows and columns in Excel
Copying, pasting and moving cells are simple everyday tasks for an Excel user. But what if something already occupies the area we want to paste to? We have to move things into temporary locations before putting them into their final destination. Wouldn’t it be better if there were a simple way to swap ranges, rows Read More
VBA Tables and ListObjects
Tables are one of the most powerful features of Excel. Controlling them using VBA provides a way to automate that power, which generates a double benefit 🙂 Excel likes to store data within tables. The basic structural rules, such as (a) headings must be unique (b) only one header row allowed, make tables compatible with Read More
How to add a prefix or suffix to every cell using VBA
We can often find spreadsheets with cells containing codes that have been shortened. So, rather than Proj003412, the cell value has been reduced to just 3412. It’s not a big problem to extend the code back to the full version; we could achieve that with a simple formula, then copy and paste special values. If Read More
Apportion a value across cells with VBA
Today, I want to share a really useful time-saving macro with you. It’s a macro to apportion a value across cells. Now that I’ve written it, I’m starting to gain the benefits, and hopefully, you can too. The filename for this post is 0005 Apportion value across cells.zip. When to use the macro? Let’s say you’ve Read More
Loop through each item in a Data Validation list with VBA
I had an interesting question from a reader recently. He wanted to save a PDF for each item in a data validation drop-down list. In the past, when I have tackled similar tasks, I have always used a named range with all the possible values, then looped through the cells in the named range. My Read More
VBA code for charts and graphs in Excel
Charts and graphs in Excel have hundreds of different options. This is great for creating precisely the visualization we want but can be time-consuming to apply. When we want to apply those hundreds of settings to lots of charts, it can take hours and hours of frustrating clicking. The short code snippets below will help Read More
Convert Merged cells to Centre Across Selection with VBA
To merge or not to merge? That is the question. And it’s a questions which divides the Excel community. We find the Merge & Center button is on the Ribbon, so it appears to be the only option. But soon we are greeted by these types of annoying error messages: Excel has a secret alternative Read More
VBA code to select all merged cells
Merged cells can be frustrating when using VBA code, as they just don’t operate in the same way as normal cells. Even when using Excel without VBA, merged cells cause a whole host of frustrating error messages. Even if you don’t use them, lots of other users do. So you will encounter merged cells in Read More
