none
Powershell, Excel, Autofilter on multiple items RRS feed

  • Question

  • #filter for column
    $FL = @("lastname, first","lastnamedif, firstnamedif") 
    $rng=$Worksheet.cells.item(2,38).entirecolumn
    $rng.select | Out-Null
    $excel.Selection.AutoFilter(17,$FL)
    

    Hello, i have a scrip that is data mining in Excel. I need to filter a column based on multiple data, sometimes, two, or maybe more data. i have tried many different methods and i either get only the last value in the array. or an error - {Exceoption calling "Autofilter" with "3" arguments(s): "Autofilter method of Range class failed". Please help. I have found nothing on the Thanks , Jeff W.  -------------------------------------------------------------------------------- Jeffrey I Wilson

    Jeffrey I Wilson

    • Moved by Steve Fan Friday, August 22, 2014 2:14 AM script related
    Thursday, August 21, 2014 2:49 PM

Answers

  • Well, after adding a space after the comma, it worked, eureka !!!!!!! thank you so much, been pounding my head for DAYS.... :) very happy. I will mark as answered,

    Here is the code

    $xlFilterValues = 7 
    #filter for column 
    $FL = @("value 1", "value2") 
    $rng=$Worksheet.cells.item(2,38).entirecolumn 
    $rng.select | Out-Null 
    $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)


    


    Jeffrey I Wilson

    • Marked as answer by blackd1 Wednesday, August 27, 2014 3:12 PM
    Wednesday, August 27, 2014 3:12 PM

All replies

  • Hi,

    Since this issue is more related to Excel development, I'll move it to the Excel for Developers forum:

    http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualifiedpool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Steve Fan

    Forum Support


    Come back and mark the replies as answers if they help and unmark them if they provide no help.

    If you have any feedback on our support, please click here

    Friday, August 22, 2014 2:12 AM
  • Hi,

    We have to set the 3rd argument "Operator" of the AutoFilter method when filtering with an array as criteria. Please try the code below.

    $xlFilterValues = 7
    #filter for column
    $FL = @("lastname, first","lastnamedif, firstnamedif") 
    $rng=$Worksheet.cells.item(2,38).entirecolumn
    $rng.select | Out-Null
    $excel.Selection.AutoFilter(17,$FL,$xlFilterValues)


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 22, 2014 6:28 AM
    Moderator
  • Hello,

    Thank you very much for the sample code, but it only choose the last in the  array... the other , others are ignored... Please help. Very frustrated. and also need to get this working. . Any other suggestions?? again thank you for your response... and for correcting the fourum i am to post in. I am new to Posting for help. Been trying to rack my head by myself and decided to "Post for help"..

    Thanks,

    Jeff W.


    Jeffrey I Wilson

    Wednesday, August 27, 2014 3:05 PM
  • Well, after adding a space after the comma, it worked, eureka !!!!!!! thank you so much, been pounding my head for DAYS.... :) very happy. I will mark as answered,

    Here is the code

    $xlFilterValues = 7 
    #filter for column 
    $FL = @("value 1", "value2") 
    $rng=$Worksheet.cells.item(2,38).entirecolumn 
    $rng.select | Out-Null 
    $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)


    


    Jeffrey I Wilson

    • Marked as answer by blackd1 Wednesday, August 27, 2014 3:12 PM
    Wednesday, August 27, 2014 3:12 PM
  • >>after adding a space after the comma, it worked, eureka !!!!!!! <<

    I don't think losing a space after the comma is the reason of un-working. After testing with the line below in my machine

    $FL =@("Jul","May")

    it works to filter.

    Thursday, August 28, 2014 1:07 AM