#2 The power of Autofilter in VBA - Part 2

  • #1 Let the users only have access to one field for their selection of records.
    We assume here that we have four columns and that the sheet only contains data in the table.

    #2 Autofilter & Protected worksheet
    If we only want the end-users to filter and view data then we can apply a technique that allow users to do it although the worksheet is protected.

    Depending on the situation it may be necessary to associate the code to other events then what the example use below:

    1. Option Explicit
    2. Private Sub Worksheet_Activate()
    3. With Me
    4. If .AutoFilterMode = False Then .UsedRange.AutoFilter
    5. .Protect UserInterfaceOnly:=True
    6. .EnableAutoFilter = True
    7. End With
    8. End Sub

    #3 Looping through the collection of fields & conditions
    Sometimes it may be useful to filter the data based on several fields and with one condition per field.

    The following example shows how we can do it quite easily. For the solution to be more flexible, the conditions can be retrieved by letting the users add them via an Inputbox.

    The third condition in the example, i e "<>", is interpreted by Excel as not empty.

    #4 Delete records (rows)
    There are several situations where we need to delete records, both records that are empty as well as records that contain data we don't want or need to work with.

    The example below shows how to delete records with one condition.

    Q: How can we delete records that have no data ( i e are empty) in one or several fields?
    A: Use the above approach but replace the condition with "="!

    #5 Do the arithmetic
    Yes, I´m aware of the so called database-functions that Excel provides, but sometimes we need to do it a little bit different than the "usual way".

    Except for that, I have, lately, become more convinced about the need for doing the aritmethics in VBA than add worksheetfunctions directly into to the worksheets.

    Why? Well, from my point if view there are two major arguments for it:

    When workbooks becomes larger and larger (due to the amount of data involved) we need to re-think how we approach the work to be done. One way is, for example, to avoid adding 1000 complex formulas into one worksheet and only calculate when we need to review the output.

    Many systems produce an output of data that need to be re-arranged, manipulated or filtered before we can do the aritmethic.

    Having said that, it's time for an example!

    Here we assume that we regular import updated data from a system (i e a database) and that the article names also include the sizes of the cloths, like "xxxxxx Size xxx".

    Every size is summarized in two dimensions:
    - The storage-number
    - Number of placed orders

    #6 Transfer & Transform data
    In the last example, which is also the most advanced, the following terms hopefully will explain it.

    We regularly import data into one worksheet. Except for the first column of data, we need to transform the data from columns to rows, when transfering it to a second worksheet.

    The unique collection of items we create from C-column in the first worksheet becomes fieldnames in the second worksheet.

    Please note that it's faster to transfer values then to copy value from one location to another.

    # Remarks
    Keep in mind that we always need to have fieldnames (i e columnnames) in the first row when working with the Autofilter-tool.

    The Autofilter has one major limitation and that is it can only handle two conditions for each field at the same time.
    This may or may not be a problem but we should be aware of this limitation when we plan to use the AutoFilter in VBA.

    Perhaps it's time for MS to extend the number of conditions in the next coming version.

    # The End - Don't forget to enjoy life - Turn off the computer(s)!

    Enjoy autofiltering!