topic Re: I want to uncheck the filter value from a specified column in Excel. in Product Forum

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

Karthik.choppadandi — Tue, 11 Jun 2024 09:52:27 GMT

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

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

Mohamad_Dakkouri — Tue, 11 Jun 2024 13:55:29 GMT

Hi @Karthik.choppadandi

You mean turn the autofilter off ?

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

Karthik.choppadandi — Tue, 11 Jun 2024 14:17:31 GMT

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..

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

Mohamad_Dakkouri — Tue, 11 Jun 2024 14:35:38 GMT

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')"

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

Karthik.choppadandi — Tue, 11 Jun 2024 16:27:07 GMT

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..

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

Mohamad_Dakkouri — Tue, 11 Jun 2024 19:18:31 GMT

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})

Dim filterExpression As String = ""

For Each criteria In strList

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

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

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()

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

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

Karthik.choppadandi — Wed, 12 Jun 2024 05:31:17 GMT

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