I want to uncheck the filter value from a specified column in Excel.

[Karthik.choppadandi]

I want to uncheck the filter values from a specified column in Excel. 

[Mohamad_747]

Hi @Karthik.choppadandi 

You mean turn the autofilter off ?

[Karthik.choppadandi]

No, I want to uncheck the few values from the particular column. For example I want to uncheck "Completed" and "Cancelled" please find the screenshot..

[Mohamad_747]

Hi @Karthik.choppadandi 

Why you dont get the worksheet as collection and then you apply filter inside the collection ?

You can do what you want by using a CUSTOM CODE STAGE on Excel VBO but you have native action that can do what you want.

If you want a CUSTOM code i can help you.

But if you prefer the native action in Blue Prism you can do this step : 

Drag and drop MS Excel VBO :

- Create instance

- Open Workbook

- Get Worksheet as collection 

- Close Workbook

- Close instance 

Then drag and drop an action and select

Collection Manipulation and then Filter Collection.

Put in your filter 

"[ColumnName] Not in ('CANCELED','COMPLETED')"

[Karthik.choppadandi]

Thank you Mohamad,
I can't do that one. Because I have to apply a multiple filters to the multiple columns at the same time, I have a vba custom code to select multiple filter values and at the same time I want to exclude few values from multiple columns. So, that It will reflect the values in the other Sheet..
please help me with your custom code for excluding a values in a column..

[Mohamad_747 · Accepted solution]

Hi @Karthik.choppadandi 

Sure follow this steps : 

1 : Open Ms Excel VBO and add a new page Apply Filter like this

2. Add a code stage, name it apply filter, and provide this input see below: 

in the code part put this : 

Dim ws as Object, strList as Object

dim ColNum as Integer

Dim dt As Object

message = ""

ws = GetWorksheet(handle, workbookname, worksheetname)

Try

If (criteriastring<>"") Then

strList = Split(criteriastring,"|",-1)

ColNum = ws.Range(range).Find(columnname).Column

dt = New System.Data.DataTable()

        dt.Columns.Add(columnname, GetType(String)) 

        Dim cell As Object

        For Each cell In ws.Range(range).Columns(ColNum).Cells

            dt.Rows.Add({cell.Value}) 

        Next

        Dim filterExpression As String = ""

        For Each criteria In strList

            If filterExpression <> "" Then filterExpression &= " AND "

            filterExpression &= "[" & columnname & "] <> '" & criteria & "'"

        Next

        Dim filteredRows() As System.Data.DataRow = dt.Select(filterExpression)

        Dim filteredValues(filteredRows.Length - 1) As String

        For i As Integer = 0 To filteredRows.Length - 1

            filteredValues(i) = filteredRows(i)(columnname).ToString()

        Next

ws.Range(range).AutoFilter (Field:=ColNum, Criteria1:=filteredValues, Operator:=7)

success = True

Else 

success = True

message = "CriteriaString should not be blank"

End If

Catch ex as exception

success = False

message = ex.Message

End Try

Finally it will look like this : 

Publish your action

and go to studio now

In my case i have this excel file 

I will remove from column Education this 2 values [Doctoral and Lower secondary]

Make your process look ike this : 

Provide this parameters for the action Apply Filter like this : 

if you want to remove Completed and Canceled you provide this CANCELED|COMPLETED for Criteria String

Now save and run your code it will work 😉

Please note that this action could take time because we are making a loop if you want to make the bot faster you can use oledb but you need the provider 😉

Regards

[Karthik.choppadandi]

Thank you so much Mohamad, Your code is working as expected.. 🙂