locked
Excel VBA - How to filter data? RRS feed

  • Question

  • I'd like to filter for Column E (5?) is not blank nor zero.  I'm finding the filtering help confusing. Can somebody show me how to set that filter and then how to clear it?

    When you sort filtered data, does it only sort the visible data?  or does it also sort the hidden data too?

    Thanks


    ajw



    • Edited by Geek 2016 Friday, August 3, 2012 5:00 PM
    Friday, August 3, 2012 4:58 PM

Answers

  • You said " I'm finding the filtering help confusing". Did you mean Advanced Filtering. If so then set up as per the following pictures. I have used column A so that the pictures will fit on the post including the column and row Id's.

    Create the criteria columns somewhere on the worksheet like the following. Note that they must have the same column header name as the column being filtered.

    Open Advanced filter and set the parameters. You can click the icon at the right of the field and you can then select the ranges rather than type them in. After selecting the range click the icon at the right of the field to return to the dialog as displayed in the picture.

    Note the option to copy the output to another location. If you select this option then in the Copy to field, click the icon at right of field and just select the first cell of where you want the output then close that field by clicking the icon at the right to return to dialog box as displayed below.

    Click OK in the Advanced filter dialog box.

    The instructions above are for when using "And" operator. (ie. <> 0 And <> Blank). If using Or operator like = 2 Or =4 then set criteria in one column with the Or criteria on separate rows. Equal signs do not diaplay on the worksheet and are not required when setting equal criteria.

    When using And, the criteria values are in separate columns side by side. When using Or you use multiple rows in the same column.

    Due to a limitation of 2 pictures per post I cannot add another picture of the Or in this post.


    Regards, OssieMac

    Monday, August 6, 2012 10:06 PM

All replies

  • If you do not know what is that (not empty but filled cell), you can have that situations:

    sub select_fail_Cells()
    Dim WorkRange As Range
    Set WorkRange = Selection
    Dim a As Range
            For Each a In WorkRange
                a.Formula = Application.Clean(a.Formula)
            Next a
    end sub

    Run this code after select fail cells.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, August 3, 2012 8:47 PM
    Answerer
  • The answer given by VBATools does not seem to be related to what I am interpreting as the question. I am wondering if the answer was placed against the incorrect post.

    I am assuming that you mean Filter using AutoFilter. Is that correct? If so then the following steps to manually set the filter. If you want VBA code then record the code while setting the filter.

    If using xl2007 or xl2010

    1. Turn on Autofilter if not already on (Click on a coumn header and then on Home ribbon, Editing block far right, Sort & Filter icon -> Filter)

    2. Select drop down on column E

    3. Select Number filters -> Does not equal. Enter 0 (zero) in the value field (or select zero by the drop down in the blank field)

    4. Select "And" button (default)

    5. Next row Select Does not equal in the bottom dropdown and leave value field blank.

    6. Click OK.

    If using earlier version of XL

    1. Turn on AutoFilter if not already on (Click on a column header then Menu item Data -> Filter -> AutoFilter

    2. Select Drop down on column E

    3. Select Custom

    4. First dropdown select Does not equal

    5. Enter 0 (zero) in the field to right.

    6. Select "And" button (default)

    7. Next row dropdown select Does not equal and leave field to right blank.

    8. Click OK.

    To clear Filters simply select the drop down and in xl2007 or xl2010 check the box against "Select All" and in earlier version of xl  just select "All"

    Alternatively you can turn off the AutoFilter altogether the same way as turning on. (It toggles On and Off)

    Sorting filtered data does not appear to include the hidden rows. Sorts only the visible data.

    Added with Edit after initial post: If you have further problems then please tell us what version of Excel you are using.


    Regards, OssieMac


    • Edited by OssieMac Saturday, August 4, 2012 7:06 AM
    Saturday, August 4, 2012 7:04 AM
  • Hi.  Thanks for the response.  I was hoping not to use the AutoFilter.  Is there a way to do it w/o AF?

    ajw

    Monday, August 6, 2012 1:59 PM
  • You said " I'm finding the filtering help confusing". Did you mean Advanced Filtering. If so then set up as per the following pictures. I have used column A so that the pictures will fit on the post including the column and row Id's.

    Create the criteria columns somewhere on the worksheet like the following. Note that they must have the same column header name as the column being filtered.

    Open Advanced filter and set the parameters. You can click the icon at the right of the field and you can then select the ranges rather than type them in. After selecting the range click the icon at the right of the field to return to the dialog as displayed in the picture.

    Note the option to copy the output to another location. If you select this option then in the Copy to field, click the icon at right of field and just select the first cell of where you want the output then close that field by clicking the icon at the right to return to dialog box as displayed below.

    Click OK in the Advanced filter dialog box.

    The instructions above are for when using "And" operator. (ie. <> 0 And <> Blank). If using Or operator like = 2 Or =4 then set criteria in one column with the Or criteria on separate rows. Equal signs do not diaplay on the worksheet and are not required when setting equal criteria.

    When using And, the criteria values are in separate columns side by side. When using Or you use multiple rows in the same column.

    Due to a limitation of 2 pictures per post I cannot add another picture of the Or in this post.


    Regards, OssieMac

    Monday, August 6, 2012 10:06 PM