This page contains resources that I have found useful in my personal and professional life. It contains everything from quotes and books that I like to podcasts and videos.
If you don’t want to scroll through the entire list, you can click on one of the links below to filter the resources by specific category.
I have found out yet another feature of the Name Box.
The Name Box is on the left of the Formula Bar and above the column letters – see image below.
During a recent macro webinar I tried to create a new range name called Test using the Name Box.
But I also has already created a macro called Test as part of the training.
As soon as I pressed Enter after typing Test into the Name Box to create the Test range name, I was magically transported to the VBA window to the Test macro – Wow!
This means you can’t create a range name in the Name Box that is the same as a macro name.
You have to use the Define name icon on the Formulas tab to do that.
To quickly select a column of data in a formatted table you have a couple of options.
Keyboard
Select a cell in the column and press Ctrl + Space Bar.
This will select the column of data. If you want the heading too, press it again.
You can also select multiple columns before using the shortcut.
Mouse
This technique can take practice if your headings are in row 1.
If the heading starts in row 2 or below it is easier. See image below.
If you point to just above the heading row you will see a downward facing, black arrow. Click this once to select just the data. Click it again to include the heading.
When the heading row is in row 1 you need to do the same but make sure the column letter doesn’t highlight.
The image below is the correct arrow – this will select the column in the table only.
In the image below the arrow shown (because the column letter is highlighted) will select the whole column, not just the data in the table.
When creating long VBA code it is common that the start of an If statement and the matching End If statement may not be visible on the same screen.
When scrolling around trying to understand your code it can be useful to include the If statement itself as a comment following on the same line as the End If command – see examples below.
If x=0 Then'lots of codeIf y =1 Then'lots of codeEndIf'If y =1 thenEndIf'If x=0 Then
If x=0 Then
'lots of code
If y =1 Then
'lots of code
End If 'If y =1 then
End If 'If x=0 Then
The apostrophe is used to specify the start of a comment – you can have a comment following a line of code.
This structure can assist when trying to identify which End If statement relates to which If statement.
A trick to stay in the cell you are editing is to hold the Ctrl key down when you press Enter.
You Can Undo After You Save
I am amazed how few people know this.
Way back in Office 2007 Microsoft changed the Undo List so that it is NOT cleared whenever you save a file.
You can use Ctrl + z or the Undo icon to undo things you did before you saved the file.
If you close the file that obviously clears the Undo List.
Please let people know this as I find so many people in my training sessions do not know things have changed since Office 2003.
This applies to all MS Office apps.
Clear Borders In Excel
If you need to clear all the borders from a selected range use
Ctrl + Shift + _ (underline)
Make Excel VBA Pause
Sometimes when running a macro you need to make sure Excel has had time to do something before progressing.
This is typically in large models were it can take time (a few seconds) to do a specific task eg removing a filter or updating an external data source.
You can pause a macro to allow Excel to do something by using the Wait command.
Application.Wait (Now + TimeValue("0:00:02"))
The above code will pause the macro for 2 seconds.