VBA Window Tip
Did you know the right-hand side drop-down, above the code window, lists all the Subs and Functions in a module? Now you do.
Putting Excel into Lock Down
There are a few lock downs in place around Australia at the moment, so let’s look at putting Excel into lock down.
(more…)Speed up a macro
Just saw an Excel newsletter post from Kevin Jones from https://www.dataautopros.com/about-us/ He found that turning off the VBA interactive setting can speed up some macros. I tested it on a one minute macro and it cut it down to 40 seconds. Worth a try if you have a longer running macro. You can add it to your opening and closing routines. Code to turn it off. Application.Interactive = False And then turn it back on at the end of your…
One Minute to Excel #15 – Replace Colours
Replacing colours manually can be a tedious task.
Did you know you can use Excel’s built-in Find & Replace to do the job for you?
(more…)Confirming Names are Unique in Excel
If you have a list of first names and last names and you want to make sure the list has no duplicates you can use a formula to confirm the names are unique.
(more…)One Minute to Excel #14 – Flash Fill
Joining names; extracting codes or converting dates is usually done with formulas, but there is now a formula-free solution called Flash Fill.
(more…)Make Sure All Input Cells Have an Entry in Excel
When creating an input range you may need to validate input cells. That may mean ensuring all input cells have an entry. Here’s how.
(more…)Name box shortcut
Just found out you can double click the re-size control on the Name Box. This quickly shrinks the Name Box width. I have no idea how long that feature has been there, but I have just found it. Woohoo!
How Many Rows and Columns in a Range
Excel has two functions to answer these questions. (more…)