Looping through worksheets is one of the most common actions performed by those who use VBA to automate Excel. However, we don’t always want to apply a macro to every worksheet; sometimes, we only want it for each selected sheet. Depending on the action being performed, this can cause an error. This occurs where the … Read more
In this post, I want to answer a question asked by a reader. The question was from Nick: “I have a chart on a separate chart sheet, where I’d like to add a developer object (button, option box or checkbox) to control whether data is shown quarterly or monthly. Any idea how to do that?” … Read more
This is the first of two posts showing how to split and combine rows from/to delimited cells. In days gone by, it would take a long time for us to resolve these scenarios. But with Power Query, it’s now a quick and easy process, which can be updated with a simple refresh. In this first … Read more
If you’re like me, you build Power Query solutions in a test environment first; then, when ready, it’s released into the wild. This means the paths of all source files must be updated. Or maybe you’re a consultant who creates solutions for others; when you distribute the workbook to your client, you have to provide … Read more
Office 365 subscribers now have Excel’s new calculation engine available to them, meaning lots of users suddenly have access to dynamic arrays. This got me thinking about whether VBA user-defined functions (UDFs) can make use of the new functionality too. I’m not an expert in this; I’m just sharing as much as I’ve learned to … Read more
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
In Excel, we can easily create absolute and relative references. A simple $ symbol here and there is all it takes. But Power Query is less flexible, therefore getting values from other rows is not as simple. So, in this post, we cover how to get Power Query absolute and relative references. Download the example … Read more
In this post, I want to cover a common reporting requirement, which is a Power Query running total. I have previously covered a similar topic in a post about running totals in tables; however, those methods don’t work in Power Query. So, we’ll need to find an alternative approach. Both Tables and Power Query were … Read more
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
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. Download the example file: Join the free Insiders Program and gain access to the example file used for this … Read more