Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter November 2006

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

Newsletter Index


Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download


1) SparkMaker : SparkMaker is an add-in for Microsoft Office that lets you create your own spark lines in Excel, Word, PowerPoint, and HTML documents....


Got any Excel Questions? Free Excel Help

This month, due to time restraints, I'm going to show you an idea for the creation of Gantt charts, also known as timelines,

from Andy Pope. It involves the use of: VLOOKUP, Conditional Formatting, INDEX and MATCH .

Andy answered a question about the creation of Gantt charts in the OzGrid free Excel question and answer forum .

The  question and answer can be seen here . There is also a download in post 4 from Andy


If registering on the forum, please do read the all the rules though as we enforce them with vigor

If visiting the forum and have any questions regarding this, or any other problem, DO NOT REQUEST HELP DIRECTLY FROM ANDY!!!

You are free to post questions, ***but asking for Andy will not be tolerated***. He is a busy man and answers questions on the OzGrid free Excel question and answer forum most days. Abuse of his kind nature will result is permanent ban. Also, by posting a question please do NOT assume it WILL be answered.

Excel Dashboard Reports & Excel Dashboard Charts


Excel VBA & AutoFilter | Excel VBA AutoFilter Criteria | Excel VBA AutoFilter Dates | AutoFilter by Date & Time

I thought I would show you how we can use VBA to easily identify a table that has been filtered. By this I mean identify which field is using what criteria.


The 1st one we will use is a custom Excel function . This will not only flag the correct criteria field but also tell us what the criteria is. Let's assume the table to filter occupies A1:E1000 with A1:E1 being headings. First we should always have at least 3 rows above any table in Excel. Select rows 1:3 and go to Insert>Rows. This will insert 3 rows, or as many as you select. We will use at least 1 row to display the criteria in the appropriate column. Why at least 3 rows then? Good practice is the answer. This way we can set up criteria, if need be, for Advanced Filter .CRITERIA FUNCTION CODE

Below is the code that must be added to the Workbook, or an Excel Add-in . To add the code to a Workbook go to Tools>Macro>Visual Basic Editor (Alt+F11) then to Insert>Module and paste in the code below;

Function AutoFilter_Criteria(Header As Range) As StringDim strCri1 As String, strCri2 As String    Application.Volatile    With Header.Parent.AutoFilter        With .Filters(Header.Column - .Range.Column + 1)            If Not .On Then Exit Function                strCri1 = .Criteria1            If .Operator = xlAnd Then                strCri2 = " AND " & .Criteria2            ElseIf .Operator = xlOr Then                strCri2 = " OR " & .Criteria2            End If        End With    End With        AutoFilter_Criteria = _     UCase(Header) & ": " & strCri1 & strCri2End Function

Note the use of Application.Volatile in the code. This will ensure our function updates whenever the AutoFilter criteria changes. Ok, ensure you have AutoFilter applied to your table. If you don't, the function will return #VALUE! Now, in, say A1 Enter =AutoFilter_Criteria(A4) (A4 is the 1st heading) and copy across as many columns as you have headings. Now simply filter by any criteria and the function will display your criteria in the relative column cell.


This one can be used in addition to the custom function above, or on its own. However, you really should have at least 1 volatile function on the Worksheet it is used in. To ensure this simply Enter =TODAY() in any cell. Right click on the Worksheets name tab, choose View Code and in here paste the exact code below.

Private Sub Worksheet_Calculate()Dim lFilt As Long, lFiltArrows As LongDim lFiltRow As Long    On Error Resume Next    Application.EnableEvents = False    lFiltRow = Me.AutoFilter.Range.Row    lFiltArrows = Me.AutoFilter.Filters.Count        Range(Cells(lFiltRow, 1), Cells(lFiltRow, _        lFiltArrows)).Interior.ColorIndex = xlNone        If Me.FilterMode = True Then            For lFilt = 1 To lFiltArrows                If Me.AutoFilter.Filters.Item(lFilt).On Then                    Cells(lFiltRow, lFilt).Interior.ColorIndex = 46                End If            Next lFilt        End If    Application.EnableEvents = True    On Error GoTo 0End Sub

Come back to Excel and again filter your table. You will note that this one will automatically detect your headings which have AutoFilter applied. When no criteria is set, in others words not filtering, no color will change.

Until next month, keep excelling!

Got any Excel Questions? Free Excel Help

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!

Software Categories Search Software

Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software || Outlook Add-ins

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.

Contact Us