OzGrid Excel and VBA Newsletter

All Excel Templates Normally an 80% saving at $299.00. Super Special $127.00! Best Value on the WWW More Hot Specials Here!

We Recommend SmartDraw for Flowcharts

SmartDraw is the quick and easy way to draw quality flowcharts, org charts, web graphics, and business presentations. You can try SmartDraw free for 30 days and see why it was voted "Best Business Program" two years in a row. For business charts and diagrams- ISO 9000 Flowcharts, Floor Plans, Circuit Diagrams, Flow Charts, Org Charts, VisualScript XML, Floor Plans, Business Forms, Network Diagrams, Circuit Diagrams, Engineering Diagrams, Flyers, Maps, Timelines, Clip Art, and Web Graphics and MUCH MORE

Excel HTML/JavaScript | Excel ASP.Net |Excel Java (J2SE/J2EE) | Excel J 2ME |Excel for Java Phones

MAIN INDEX

What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks

What's New at OzGrid

Firstly we must wish all of our visitors a Safe and Happy New Year. We hope that you had a wonderful, restful Christmas and are all fired up and ready to face 2004.

We had a brief interlude over the Christmas and New Year period, but in the last week or so things Start ed getting busy again.

Over the Christmas period we changed servers for our web-site which went extremely smoothly until such time as we needed to try and upload the database for theQuestion Forum . This took a little longer than expected, and at one point it looked like we were going to loose the lot. Fortunately persistence paid off and all should now be back to normal.

Our book "100 Excel Hacks" is about two thirds of the way through, with us entering the extremely time-consuming "editing" stage. We hope that it will be wrapped up in the next few months, so we will keep you informed of a likely publication date in forthcoming newsletters.

Very large drop in ALL our training prices, these can be viewed here These price will remain until we have completed the book. About another month or so.

Excel Tips and Tricks Subtotal Function made Dynamic

Barcode, Fonts, ActiveX, DLL's, Labels and more!

The SUBTOTAL Function in Excel is used to perform a specified function on a range of Auto Filtered cells. When the Auto Filter has been applied the SUBTOTAL function will only use the visible cells, all hidden rows are ignored. The operation performed is solely dependent on the number (between 1 and 11) that we supply to its first argument Function_num For example;=SUBTOTAL(1,A1:A100)

will Average all visible cell in the range A1:A100 after an Auto Filter has been applied. If all rows in A1:A100 are visible it will simply Average them all and give the same result as =AVERAGE(A1:A100)

The number for the first SUBTOTAL argument, Function_num, and it's corresponding function are as shown below

Function_Num	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP

As we only need to use a number between 1 and 11 we can have the one SUBTOTAL function perform a function of our choice. We can even make the choice from a drop-down list which resides in any cell. Here is how;

Add all the function names, in the same order as above, to a range of cells. I will use D1:D11
With this range selected, click in the Name Box (white box left of the Formula Bar) and type the name: Subs and then click Enter.
Select all of Column D and go to Format>Column>Hide
Go to View>Toolbars>Forms and then click on the Combo box Control and click cell C2
Use the Size Handles to size the combo box so it can display the longest function name, i.e AVERAGE
Right click on the Combo box and choose Format control then the Control tab.
In the Input range: type: Subs In the Cell link: box type:$C$2 Change the Drop down lines: to 11
In cell C3 Enter this formula: =IF($C$2="","","Result of "&INDEX(Subs,$C$2))
In cell C4 Enter this formula: =IF($C$2="","",SUBTOTAL($C$2,$A$4:$A$100)) Where $A$4:$A$100 is the range the SUBTOTAL should act on.

Now all you need to do is select the required SUBTOTAL function from the Combo box and the correct result will be displayed.

Excel, Word, Access Password Recovery ||Corrupt Excel, Word, Access File Recovery

Excel VBA Tips and TricksGrouping data onto individual sheets

This month I thought we would look at how we can use Excel VBA to take a table of data and put all data, that meets a criteria, onto their own Worksheets. For the exercise I will use data as shown below.

Download Working Example We will write some VBA code that will place all data that fits under a criteria of "Administration" onto a Worksheet named "Administration", all data that fits under a criteria of "Marketing" onto a Worksheet named "Marketing" etc. To do this we will use the AdvancedFilter Method.First we need to name the table, including row 4 headings, MyData. Then we need to run the code as shown below on the data.

Sub GroupData()
Dim rData As Range
Dim strName As String
Dim lLoop As Long, lCount As Long

Set rData = Range("MyData")

'Ensure column "G" is clear
Sheet1.Range("G:G").Clear

'Filter out a unique list of Departments to range G1
rData.Columns(5).AdvancedFilter xlFilterCopy, , Sheet1.Range("G1"), True

'Count how many unique departments there are. This number is used _
to determine how many loops are needed.
lLoop = Sheet1.Range("G2", Sheet1.Range("G65536").End(xlUp)).Rows.Count

     For lCount = 1 To lLoop
    'Get the name of top department from cell G2 (G1 is a heading)
     strName = Sheet1.Range("G2")

    'Create a sheet named the same as the department. _
     If sheet already exists halt macro
     On Error Resume Next
     Sheets.Add().Name = strName
      If ActiveSheet.Name <> strName Then 'Sheet already exists
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        MsgBox "Sheet already exists. Macro stopped", vbCritical, "OzGrid.com"
        Sheet1.Range("G:G").Clear
        On Error GoTo 0
        Exit Sub
      End If

      'Use the unique list of Departments as the _
       criteria for Advanced Filter
      rData.AdvancedFilter xlFilterCopy, Sheet1.Range("G1:G2"), _
      Sheets(strName).Range("A1")
      'Delete cell G2 so that next Department moves up
       Sheet1.Range("G2").Delete xlShiftUp
    Next lCount

'Clear criteria heading
Sheet1.Range("G1").Clear
Sheet1.Select
End Sub

If you are not familiar with the AdvancedFilter method in Excel VBA read the help file. It is a very good method to familiarize yourself with and can help in many situations. Alsogo here and read the Excel Tips and Tricks section on Advanced Filter.You can download a working example of the above codehere

Are You Into Excel and Excel VBA?Our 4 Most Popular Bundled Savings

Until next month, keep Excelling!

You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :

Microsoft Excel Add-ins	Financial Add-ins and Software
Microsoft Excel Training & Tutoring	Conversion Software
Microsoft Excel Templates	Construction and Estimating Software
Excel, Word, Access Password Recovery	Financial Calculators
Corrupt Excel, Word, Access File Recovery	Business Planning
Excel & VBA books	Excel on the WWW
Windows & Internet Software	Downloadable Excel Training
Barcode, Fonts, ActiveX, labels and DLL's	OUR CURRENT SOFTWARE SPECIALS!
Chart Tools & Add-ins	The Analysis Add-ins Collection
Trading Software & Add-ins	TOTALLY FREE 24/7 EXCEL, VBA SUPPORT!