Spy Journal 3.0 Filtering the web for you

Andrew recently posted a piece of VBA code used to create dynamic ranges. Very useful.
Here is my original post on dynamic ranges posted back in 2004 repeated.
If you have used a range name before then you will know that it can be frustrating updating the reference of the range name if you want to add data to the range.
Here are some ways you can dynamically update the range by using the OFFSET function in the range name reference.
Assume for all these examples that column A has a mixture of text and numbers for several cells.
Click Insert - Name - Define on the menu.
In the Names in Workbook Dialog box type a range name (Eg test_range) and then try these different options.
1: Expand Down as Many Rows as There are Numeric Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)
2: Expand Down as Many Rows as There are Numeric and Text Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

Gone out back later - getting new car - finally!

We just finished playing a boisterous game of hide and seek - lots of fun and a real challenge to find the places the kids can't find me. Hiding in plain sight is often the best way.

Susan Bradley AKA the SBS diva is a SBS 2003 Legend!

Following normal Microsoft practice of patching what aint broke and then wondering why it is broke, I installed the Best Practices Analyser last week on my SBS2003 server. Previously it had been running absolutely perfectly. (well as perfectly as a Microsoft server can get - only needed restarting every 2 weeks or so)

The BPA told me my system was largely ok but I should fix some things. With the assistance of Susan I duly fixed these things.