It is our attitude at the beginning of a difficult undertaking which, more than anything else, will determine its successful outcome.

William James (1842 – 1910)

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.

Financial Modeling in Excel for Dummies (Wiley, 2017)

Disclaimer: I received a free copy of this book to review.
This book is a great introduction to Financial Modeling.
The content is easy to follow and gets you started on the right foot with lots of best practice advice.
The only downside of the book is the screen shots tend to include the whole screen rather than zoom in on the important part of the screen – many images are hard to read.
The book is a great primer for Danielle’s first book which allows you to learn even more about financial modeling.

It takes less time to do a thing right than if does to explain why you did it wrong.

Henry Wadsworth Longfellow (1807 – 1882)