OzGrid Excel and VBA Newsletter

Search Tips

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

ADD-IN CATEGORIES:  Financial Excel Add-ins | Charting Add-ins | VBA Macro Add-ins |Data Management Add-ins | Construction and Estimating Add-ins |Excel Converters |Excel trading-add-ins

MAIN INDEX

Search Tips

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

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;

1. Add all the function names, in the same order as above, to a range of cells. I will use D1:D11
2. 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.
3. Select all of Column D and go to Format>Column>Hide
4. Go to View>Toolbars>Forms and then click on the Combo box Control and click cell C2
5. Use the Size Handles to size the combo box so it can display the longest function name, i.e AVERAGE
6. Right click on the Combo box and choose Format control then the Control tab.
7. In the Input range: type: Subs In the Cell link: box type:$C$2 Change the Drop down lines:  to 11
8. In cell C3 Enter this formula: =IF($C$2="","","Result of "&INDEX(Subs,$C$2))
9. 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!

Office Ready Professional 3.0 |High Impact e-Mail 2.0 |Office-Ready Business Plans |Office-Ready Marketing Plans |e-Marketing Suite |Office Policy Manual |Ultimate Everyday Calculator |Ultimate Financial Calculator |Ultimate Marketing Calculator |Template Zone Home Page |Office Ready Stuff It |Ultimate Loan Calculator

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 :