Spy Journal 3.0 Filtering the web for you

I have been requested numerous times recently by readers in comments and emails to write some how to articles for VBA. It is my intention to start to do this.

My basic plan will be to take situations where new VBA users record some code with the VBA recorder, and then want to try and make it more robust.

An article by Nick Hodge (the Excel Nick Hodge) on Dicks Daily Dose of Excel website illustrates my point.

I probably will not write articles as long or as in depth as he has done. Instead I will focus on small parts of this code such as range selection, switching workbooks, defining variables and constants and using them etc. Specifically each article I write will have real practical application. E.g how to copy data from one file to another, how to save a spreadsheet as values for emailing etc.

In the meantime enjoy Nicks article on his coding technique particularly the latter stages where he talks about the progression in code development in VBA.

Data Filters are a very powerful way of analysing tables of data in Excel. Put simply they are a way of reducing the table down to just the items you want to see based on the selections you choose.

For example a simple table like this one, may have several hundred or thousand rows of data. However there may be only a few lines you are interested in. For example you may be only interested in people under the age of 18 or sales amounts that are negative (indicating a refund) or only see sales of red Ferraris.

Turning on the Filters

In Office 2003 Turn on the filters by selecting any cell in your table (contiguous section of data) and selecting Data, Filter, Auto Filter from the menu. (Keystroke ALT D F F)

The arrows will show up as in the next picture.

Excel uses a powerful vector system for managing formulas. Each cell can be linked to many others using formulas. In a simple way of explaining this, 2 cells can be added together in a third cell using a formula that references them. The columns are named alphabetically and the rows numerically.

Thus the first cell is known as A1.

Here are two numbers in cells.

It is easy to create a formula that adds 2 cells, E.g., =A1+A2. Entering this formula in cell A3 effectively sums the first 2 cells into the 3rd cell.