Category: VBA

If you are new to workbooks with macros here is a short 2 min video on the quick setup needed to work with them on your PC’s Excel and some basic points to be aware of…  

Tagged with:

I don’t often re-post, but hard not to with this really good post on Excel VBA speed and efficiency  Well worth the read. https://www.soa.org/news-and-publications/newsletters/compact/2012/january/com-2012-iss42/excel-vba-speed-and-efficiency/

Tagged with: ,

This is another handy personal macro I use in my day to day spreadsheeting and application building.  It sits in my Add-In which has it’s own tab on my custom ribbon, but you might just use the ribbon QAT or …

Personal Macros – Locked & Unlocked cells Read More »

Tagged with: ,

Following on from my recent post of Fix duplicated Conditional Formatting Rules , I mentioned Debra Dalgaleish’s website page had some great VBA to fix duplicated and messy Conditional Formatting.  I tweeked this code just a bit so that all …

Macro to fix Duplicate Conditional Formatting rules in Tables Read More »

Tagged with: , , , , ,

I did this very thing today, so thought I would share this tip with you as it saves me a lot of time.   Say you wanted to have this formula go into column 8 of the range you created in …

Entering a relative formula in VBA using the macro recorder Read More »

Tagged with: ,

As might know there is no autocomplete functionality with Data Validation dropdowns.  I wonder if the Microsoft development teams are considering this as it would be a wonderful feature.  I sometimes wonder if the validation dropdown was an accidental dropdown …

Autocomplete Workaround for Validation Dropdowns Read More »

Tagged with: , , , , ,

My last blog was on accessing personal macros from my custom ribbon. This blog is about where to store these personal macros.  These personal macros are the ones that save you time and tedious work.  There are basically two ways …

Personal Macros – Personal.xlsb or Add-In.xlam? Read More »

Tagged with: ,

I recently worked on a project that had three distinct and large datasets for different geographical areas in three workbooks.  Starting with the first workbook and dataset in a Table in its own worksheet, I created a worksheet of seven …

VBA to Change Pivot Table Source Read More »

Tagged with: , , ,

I monitor many Excel and VBA blogs using Feedly and see all sorts of tips, code and great solutions to collect and learn from along the way.  There is no better site for VBA than Stackoverflow.com Anyone who has used …

Tip for creating syntax in VBA for Worksheet formulas Read More »

Tagged with: , , , ,

If you are working with output from other systems (or preparing input for other systems) you might need to find certain characters that cause problems.  In my case I was creating XML code for a custom ribbon from Excel formulas …

Check range for specific characters Read More »

Tagged with: , , , , ,

This post is about a common business problem of grabbing messy data from a transactional document and updating a dataset to collect history.  In my previous life, I was in Commodity Export Marketing, so I am using the ‘real world’ …

Grabbing messy data with VBA Arrays Read More »

Tagged with: , , , , ,

There is a dangerous little trap in using Find & Select dialog on the Home Tab and then using Replace All.  I use this a lot and most of the time need to change lots of cells so in the …

Take Care with Replace All Read More »

Tagged with: , ,