none
Autofilter won't work when filtering for blank records and numbers beginning with 614 RRS feed

  • Question

  • Can someone assist please

    I need to apply an autofilter in Excel to return blank cells & numbers that begin with 614 (total number length is 11 characters).  The autofilter only works to return the blank records using the following code.

    With ActiveSheet
        .AutoFilterMode = False 'remove any active filters
        .Range("A1:O1").AutoFilter Field:=2, Criteria1:="614*", Operator:=xlOr, Criteria2:="="
    End With
       

    I have tried autofilter with arrays, changing the format of the range to Text by vba, anything and it just keeps returning only the blank records, so I know that at least that part works. 

    The issues lies with

    Criteria1:="614*"

    There is something here that vba for excel 2010 is not liking and I don't know what it is.  As referred to earlier I have tried about every suggestion that I can find but nothing is working.

    The file is a data dump from an external source and is the answer to dump this field as text initially. I am pretty sure that this is not done. 

    There is a hint (nothing explicit) somewhere that you can't use wildcards with numbers is this correct?  If so why doesn't it work when I change the cell format to text prior to applying the autofilter?

    Wednesday, May 27, 2015 12:50 AM

Answers

  • Convert the column to text using "Text To Columns" as follows:

    1. Select the column
    2. Select Text To Columns (On Data ribbon)
    3. Select Fixed width option
    4. Click Next and ensure only one column. (If additional separating lines shown then select and drag off the table)
    5. Click Next and should have screen to select Column data format.
    6. Select Text and click Finish.

    Column will now be in text format. Use following code.


    With ActiveSheet
         .AutoFilterMode = False 'remove any active filters
         .Range("A1:O1").AutoFilter Field:=2, Criteria1:="614*", _
                Operator:=xlOr, Criteria2:="="
     End With


    Regards, OssieMac


    • Edited by OssieMac Wednesday, May 27, 2015 2:22 AM
    • Proposed as answer by ryguy72 Wednesday, May 27, 2015 3:46 AM
    • Marked as answer by JC- SpeedCast Wednesday, May 27, 2015 4:01 AM
    Wednesday, May 27, 2015 2:20 AM
  • Yes you can set the text at import stage but you can set the format for each individual column and not necessary to set it for all the columns.

    Because you are using code anyway, rather than alter other code that might present even further problems, I would  convert the column back to numeric after the code involving the filtering process. Do this with the following method:

    Enter 1 (Numeric one) in a blank cell.

    Copy the cell containing the 1.

    Select the text formatted numeric data and Paste Special -> Multiply.

    The previous step will insert zeros in the blank cells but if you don't want the blanks to be zeros then use Replace and replace the zeros with nothing but ensure you set the options in replace to entire cell contents.

    The above can all be done with VBA code.


    Regards, OssieMac


    • Edited by OssieMac Wednesday, May 27, 2015 3:16 AM
    • Proposed as answer by ryguy72 Wednesday, May 27, 2015 3:46 AM
    • Marked as answer by JC- SpeedCast Wednesday, May 27, 2015 4:01 AM
    Wednesday, May 27, 2015 3:16 AM

All replies

  • Convert the column to text using "Text To Columns" as follows:

    1. Select the column
    2. Select Text To Columns (On Data ribbon)
    3. Select Fixed width option
    4. Click Next and ensure only one column. (If additional separating lines shown then select and drag off the table)
    5. Click Next and should have screen to select Column data format.
    6. Select Text and click Finish.

    Column will now be in text format. Use following code.


    With ActiveSheet
         .AutoFilterMode = False 'remove any active filters
         .Range("A1:O1").AutoFilter Field:=2, Criteria1:="614*", _
                Operator:=xlOr, Criteria2:="="
     End With


    Regards, OssieMac


    • Edited by OssieMac Wednesday, May 27, 2015 2:22 AM
    • Proposed as answer by ryguy72 Wednesday, May 27, 2015 3:46 AM
    • Marked as answer by JC- SpeedCast Wednesday, May 27, 2015 4:01 AM
    Wednesday, May 27, 2015 2:20 AM
  • Hello OssieMac

    Thanks for your reply

    I am assuming that the text format could be applied at import stage. I will test applying the format at import & also adding it to my code.  It appears I will then have to amend all my later code later on to work on text rather than values vlookups etc.

    Is this simply a case that wildcards cannot be used in relation to numbers?  Or is it that the format of the cells has to be either text or values?  The natural import appears to make the column a 'custom' format which then has issues with formatting some of the numbers as scientific notation as does making them text.




    Wednesday, May 27, 2015 2:56 AM
  • Yes you can set the text at import stage but you can set the format for each individual column and not necessary to set it for all the columns.

    Because you are using code anyway, rather than alter other code that might present even further problems, I would  convert the column back to numeric after the code involving the filtering process. Do this with the following method:

    Enter 1 (Numeric one) in a blank cell.

    Copy the cell containing the 1.

    Select the text formatted numeric data and Paste Special -> Multiply.

    The previous step will insert zeros in the blank cells but if you don't want the blanks to be zeros then use Replace and replace the zeros with nothing but ensure you set the options in replace to entire cell contents.

    The above can all be done with VBA code.


    Regards, OssieMac


    • Edited by OssieMac Wednesday, May 27, 2015 3:16 AM
    • Proposed as answer by ryguy72 Wednesday, May 27, 2015 3:46 AM
    • Marked as answer by JC- SpeedCast Wednesday, May 27, 2015 4:01 AM
    Wednesday, May 27, 2015 3:16 AM