Range Names List

If you use range names then it is a good idea to include a list of all the range names in a file for documentation purposes.

There is a shortcut that does all the work for you.

Select the cell where you want to enter the list.

Press F3 and then click the Paste List button.

Paste_List

The list has two columns. The range name and it’s reference.

The list is not dynamic. If you add, delete or change the names you will need to do the paste again.


Today’s Date

The formula that will always display today’s date is

=TODAY()

The keyboard shortcut to enter today’s date in the active cell as an input is

Ctrl + ;

The VBA line of code to enter today’s date in cell A1 is

[A1] = Date


Related Posts

“Why,” said the Dodo, “the best way to explain it is to do it”

Lewis Carroll (1832-1898) from Alice in Wonderland (1865)

VBA – Easy Way to Handle TRUE/FALSE Tests

Using a boolean variable

Let’s say you have VBA code that handles a budget and a forecast. There is a cell B2 on the Input sheet that contains the word Budget or Forecast. Based on that cell the macro with do different things. You may need to test for Budget/Forecast a few times within the code. There is an easy and flexible way to handle this.

M is for (Data) Monkey (Holy Macro! Books, 2015)

Don’t be put off by the title, this book is full of gold. It is chock full of tips, tricks and traps.

Power Query allows you to automate data cleansing operations on all sorts of Excel data – both internal and external.

Power Query can replace copying and pasting. No need for formulas and macros to get the data right.

Well worth purchasing – it will save you hours of work.

 

Right Click – Filtering

One of my favorite and frequently use right click tips is the Filter option.

You can click a cell within a table that contains the entry you want to filter by, then click the Filter option and then Filter by Selected Cell’s Value.

right click filter  Note: you can also filter by font and fill colour.