A few days ago, a reader asked a question about using parameters in Power Query; they wanted to auto-refresh Power Query whenever a parameter changes. “One question, how do you set the query to refresh automatically when a value changes.” I’ve not tried this before, but I can understand the benefits. It reduces the need … 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
Data comes in all shapes, sizes and forms, so it’s not uncommon to find a dataset with a lot of blank rows. Sometimes it’s not an issue; other times, it causes big problems. Therefore, the best practice in Excel, is to remove blank rows. If there are only a few blanks, we could do this … Read more
When creating a VBA UserForm, we generally set it to a specific size. Most other forms and windows within the Excel and Windows environments do not have a fixed sized; they can be resized by the user. With a bit of coding magic, we can achieve a similar resizing effect for our VBA UserForms. This … Read more
While working on a project recently, I thought about hiding the [X] close button which is at the top of the VBA UserForm. I managed to identify three options for achieving this. If you are thinking about the same thing, then this post will give you the answers you’re looking for. Download the example file: Join … Read more
I use the terms Sheet and Worksheet interchangeably when talking about Excel, I think most users do. Google also appears to think they are the same thing; If I search for “How to loop through Sheets with Excel VBA”, all the results returned on the first page refer to Worksheets. Yet, Sheets and Worksheets from … Read more
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
Spreadsheets are easy to break. All it takes is one press of a key and suddenly every formula is displaying in an error message. That’s not good! This is why we apply worksheet protection, to prevent user error. Some think that worksheet protection is secure, however password protected worksheets can easily be unlocked with a … Read more
I received a question from a reader recently, who wanted to sort a Listbox using VBA. As this seemed like a common problem which many users could face, I decided to dedicate a bit of time to create a more reusable solution. In this post, we will look at two different types of Listboxes: This … Read more