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.
Related Posts
Monthly Archives: June 2021
Putting Excel into Lock Down
Locking involves a format
There are a few lock downs in place around Australia at the moment, so let’s look at putting Excel into lock down.
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 code.
Application.Interactive = True
Thanks Kevin for sharing.
One Minute to Excel #15 – Replace Colours
It can be done
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?
Confirming Names are Unique in Excel
COUNTIFS to the rescue
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.
One Minute to Excel #14 – Flash Fill
Excel's Super Hero
Joining names; extracting codes or converting dates is usually done with formulas, but there is now a formula-free solution called Flash Fill.
Be excellent to one another.
Bill and Ted
Movie; Bill and Ted's Excellent Adventure
Make Sure All Input Cells Have an Entry in Excel
COUNTIF to the rescue
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.
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!
Related Posts
Excel Formatted Table Headings
No duplicates allowed
The headings in a formatted table must be unique.
How Many Rows and Columns in a Range
Two functions to the recue
Excel has two functions to answer these questions.