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
Category: VBA
Hide named ranges in Excel: 4 simple ways
Named ranges are a useful feature of Excel. We often use them to build more complex tools that we may share with other users. Therefore, those named ranges become a critical part of our spreadsheet. Unfortunately, if those users modify or delete the named ranges, it may break the calculations. So, in this post, I Read More
6 ways to make Excel sheets very hidden (invisible)
This week, while working on a project, I wanted to hide a worksheet in Excel. However, I didn’t want users to unhide it or even know it was there. I wanted to make the Excel sheets very hidden (i.e. invisible to other users). I’m sure many users know how to hide a sheet. However, most Read More
VBA Copy File – 5 examples to copy and paste
Visual Basic gives us access to parts of the Windows environment; therefore, there are lots of file-based activities we can achieve with VBA. One of these is to use VBA to copy a file. In this post, we look at 5 examples of how to achieve this. Copy a file VBA In this post, we Read More
VBA Rename File: 5 code examples
VBA allows us access to the windows environment to rename a file. In this post, we will look at 5 examples of renaming files with VBA. Rename a File VBA To rename a file with VBA we use the Name command. Example 1: Renaming a file This example renames a file from Example File.xlsx to Read More
7 ways to remove additional spaces in Excel
The space character for an Excel user can be a real problem. When exporting data from various IT systems, that export will often include extra spaces around values. Finding and removing additional spaces is a crucial skill for every Excel user. We often can’t see the space characters, but Excel sees them. This can cause Read More
Unblock Macros downloaded from the internet
Macros are a great way to automate Excel; they give us a lot of power to interact with, not just Excel but other Office applications. Unfortunately, hackers know about this power too, and can use files containing macros to distribute malware to PCs and entire networks. Microsoft are making changes so that any office files Read More
Excel – Create multiple PDFs based on a list
I was on a call recently where somebody asked if it was possible to create a unique PDF for each item in a list. The answer is “Yes”, so in this post, I want to show you how to do it; how to create multiple PDFs based on a list in Excel. Download the example Read More
VBA to sort sheets alphabetically
When working with Excel, the worksheets order matters. The tabs should reflect the flow which a user can understand. Since there is no native feature to do it, using VBA to short sheets alphabetically can be a useful time-saver. But things are never as straightforward, are they? Sorting can be in standard or reverse order Read More
VBA to hide all sheets except one
There is a common question that many Excel users ask: “How can I hide all sheets except one with a macro?”. So, let’s answer that very question. First, I’ll share with you the VBA to hide all sheets except one, and then the process to turn it into a reusable code block. Basic VBA code Read More