VBA to Clear a Filter

Using Excel’s built-in filtering can speed up your VBA code.

It is important if you are applying filters that you clear any existing filters before you apply a new filter. Otherwise the existing filters will usually affect a new filter you apply.

The line of code below will remove filters on Sheet1 (Sheet1 is the sheet code name that you see on the left side of the VBA screen – it may not be the sheet tab name).

If Sheet1.FilterMode Then Sheet1.ShowAllData

The .FilterMode property is True if a filter is in place on the sheet and False if not.

The .ShowAllData method will return an error if no filter is in place – hence the use of the If statement.

Instant Format in Excel

You may know the two keyboard shortcuts below for currency and percentage.

But what you may NOT know is a technique that has been around since the early versions of Excel.

The technique allows you to automatically apply these two formats after you type an entry.

It you type $1000 into a cell and press Enter. Excel will automatically apply the $ format to the cell. The $ sign will not display in the Formula Bar – see below.

If you type 2.5% into a cell. Excel will automatically apply the standard % format to the cell. The % sign will display in the Formula Bar – see below.

As I mentioned these are really old skills that have been lost over the years since we no longer have Excel manuals – shows my age.

Top 5 Books

A recent email from CPA Australia listed my Excel book (Advanced Excel Reporting for Management Accountants) in the top 5 of all books – see list below.

Members of CPA Australia can access electronic books for FREE via ProQuest – links are below.

You will need to log in to the CPA Australia site to be able to access them.

The accounting books CPAs love

Which books have your fellow accountants found most helpful in advancing their careers? You may find the results surprising, with their top five covering everything from Excel and analytics to forecasting and getting your own way. Try these ebooks for yourself.

Your Excel survival kit: Your guide to surviving and thriving in an Excel world

Killer analytics: Top 20 metrics missing from your balance sheet
Persuasion equation: The subtle science of getting your way
Advanced Excel reporting for management accountants
Financial forecasting, analysis and modelling: A framework for long-term forecasting

Range Selection Tip and Trick

You know how when you press Enter you usually select the cell below? You can override that without changing a single setting.

When you select a range Excel can behave differently when you press the Enter key. Not many users know this trick.

Select the range B2:G2 in a blank sheet and press Enter. The cell selected will be the one on the right or back at the start of the range depending which cell was active when the range was selected.

Pressing Enter cycles through all the cells in the range.

This works for two dimensional ranges as well – in that case the cell below is selected until the bottom of the range is reached then the top of the next column within the range is selected.

This is handy for entering data into input ranges.

Try This

Let’s say range A2:D2 has basic links referencing the cell above so cell A2 has =A1 in it.

What if you wanted to change all those relative references to fixed references?

Select the range A2:D2 and then press these three keys in sequence

F2 F4 Enter

Repeat three times – job done!

F2 is the Edit command.

F4 converts a relative reference into a fixed reference.

Enter accepts the change.

You can often achieve very fast changes with keyboard techniques like this.

For example if a cell contains an email address  or a web address but it isn’t recognised as a link, simply select the cell and press F2 then press Enter to convert it into a link.

If there is a column of them, just keeping pressing F2 and Enter to convert them all. You can become quite fast.

Marking cells Good-Bad-Neutral

Sometimes when you are reviewing a file against a printed report, you may need to identify when cell values are correct, wrong or close.

Consider using the built-in Styles Good/Bad/Neutral on the Home ribbon tab – see below.

These can be quicker than using the usual fill colour icon.

Remember they may not be useful for colour blind readers if you are sharing with other people.

Don’t forget once applied if you want to apply the same format to another cell press the F4 function key.

Once formatted you can sort or filter by colour – see right click options below.

Adding Values to Values

Let’s say you have an input cell that someone enters multiple values into eg

=10+5+34

You may have many such cells.

Now let’s say you want to add 20 to all these cells but keep the original values that have been entered in those cells.

Paste Special to the rescue.

  1. Enter 20 in a blank cell and then copy the cell
  2. Select the cells you want to amend – you can hold the Ctrl key down to select  multiple cells with the mouse
  3. Open the Paste Special dialog
  4. Click the options Values and Add – as per image below – then click OK – done!

The resulting formula will be something like

=(10+5+34)+20

 

Find and Replace Tips, Tricks and Traps

The keyboard short for Find is Ctrl + f.

For Find & Replace it is Ctrl + h.

Tips

Always, I mean ALWAYS, select the range you are working with before you run Find and Replace.

If you have a single cell selected it will affect the whole sheet – maybe not what you want.

Leave the Dialog Open

In the old days we used to close the Find dialog. Now you can leave it open if you need to change things in multiple files or sheets. You can navigate around with the dialog left open.

When using Find if you want to select all the cells it has found, click in the bottom section of the Find dialog where the cells are listed and press Ctrl + a this will select all the cells at once.

Number Formats

Be aware that sometimes numbers won’t be found due to formatting. eg if you search for 1000 but you have used the comma format eg 1,000 then the number might not be found.

You might need to do two Finds, with and without commas.

Formulas vs Values

Click on the Options button to see these options.

The default Look in: setting for Find (unfortunately) is Formulas. See image below.

This means if a formula returns what you are looking for it won’t be found. You need to switch this Look in: setting to Values.