Automate repetitive tasks and enhance functionality in Microsoft Office applications with Visual Basic for Applications (VBA).
Using loops to easily make changes across multiple worksheets Loops are one of the key tools in Excel VBA when we need to perform tasks through a number of objects (cells, worksheets, charts, workbooks etc.) . Here we will look at how to loop through Excel worksheets and workbooks. Loop Through Excel Worksheets Below you will […]
How to use SpecialCells method in Excel VBA SpecialCells in VBA is a really useful method to deploy in Excel. It returns a Range Object that only covers the type of cells you specify. You can use the SpecialCells in VBA Method to return a Range Object that only holds numbers, text, blank cells, formulae, cells with […]
In Excel there’s a lot of data being moved around, perhaps via copy and paste. If you leave a lot of stuff lying around on in memory or cache you’ll get a message along the lines of “you’ve left a lot of information on the clipboard…”. Generally in VBA you can avoid this with other […]
Ever received a spreadsheet and all the data on it is in the wrong case? You might received a dodgy dump of data from your website or database, or maybe the user-inputted data is poorly or inconsistently formatted. Here I’ll guide you through how to do Excel VBA Uppercase, lowercase and capitalising the first letter […]
Excel VBA is used for all sorts (!) of data manipulation tasks. One of the most common is sorting your data. Performing an Excel VBA sort seems trivial but some parts of it may misbehave if you’re not diligent. I’ve seen some people try to write their own sort routine in Excel VBA, but frankly […]
VLOOKUP is one of the most useful and versatile functions in Excel. As you work further with macros it’s not uncommon to make your create an Excel VBA VLOOKUP macro. With this you get the ability to reference your tables of data, but automated. Wait, what’s a VLOOKUP function? The Vertical Lookup is one of […]
Comparing and tracking changes in a spreadsheet is hard work. It’s utterly necessary but that doesn’t make it any easier. When you start adding in modifications to the VBA code underneath – which is not immediately obvious – there’s a mountain of things to screen. If a colleague has modified a module you’ll have to […]
After you’ve worked with VBA for a while, you’re going to want to prompt the user to open a file. Maybe they need to go and select the latest download from their database, or you need to import the contents of the latest sales data. Whatever the scenario, you’re going to need to access the […]
If you’ve ever found yourself repeating the same tasks in Excel—copying and pasting data, formatting sheets, or generating reports—you’ve probably wondered if there’s a faster way. That’s where Excel VBA comes in. It’s a powerful tool that lets you automate routine tasks and streamline your workflow. Whether you’re already exploring data tools like Power BI […]