cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

1 BEST ANSWER

Helpful Answers

Hi @Karthik.choppadandi 

Sure follow this steps : 

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

Mohamad_747_0-1718132570449.png

 

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

Mohamad_747_1-1718132691860.png

Mohamad_747_2-1718132719669.png

 

Mohamad_747_3-1718132871352.png

 

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 : 

Mohamad_747_4-1718132895538.png

 

Publish your action

and go to studio now

In my case i have this excel file 

Mohamad_747_5-1718132962650.png

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

Make your process look ike this : 

Mohamad_747_6-1718133132083.png

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

Mohamad_747_7-1718133158098.png

Now save and run your code it will work 😉

Mohamad_747_8-1718133355852.png

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

 

 

 

View answer in original post

6 REPLIES 6

Hi @Karthik.choppadandi 

You mean turn the autofilter off ?

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

Karthikchoppadandi_0-1718115368144.png

 

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

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

Hi @Karthik.choppadandi 

Sure follow this steps : 

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

Mohamad_747_0-1718132570449.png

 

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

Mohamad_747_1-1718132691860.png

Mohamad_747_2-1718132719669.png

 

Mohamad_747_3-1718132871352.png

 

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 : 

Mohamad_747_4-1718132895538.png

 

Publish your action

and go to studio now

In my case i have this excel file 

Mohamad_747_5-1718132962650.png

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

Make your process look ike this : 

Mohamad_747_6-1718133132083.png

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

Mohamad_747_7-1718133158098.png

Now save and run your code it will work 😉

Mohamad_747_8-1718133355852.png

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

 

 

 

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