I'm getting the above error while performing filter giving three criteria at same time in one column.
So can anyone check this code is correct or wrong and please provide solution for how to filter three criteria in one column.
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, varUsedRange As Object
Dim FilteredCount as Long
Try
sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
varUsedRange = sheet.UsedRange().address 'you can give your own Range
If sheet.AutoFilterMode Then
sheet.AutoFilterMode = False 'Turn off filters if already applied
End If
sheet.range(varUsedRange).AutoFilter (Field:=[FieldToBeFiltered1], Criteria1:=[FCriteriaToFilter1],Operator:=2, Criteria2:=[FCriteriaToFilter2],Operator:=3, Criteria3:=[FCriteriaToFilter3])
'For text, use within double quotes as Criteria1:="one"
'For multiple conditions in the same column use along with AND or OR operator as per requirement
'sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1, Operator:=xlOr, Criteria2:=2)
'If the Blue Prism Code stage doesn't recognise the Excel constants of xlAnd, xlOr then
'either get the constants from this link
'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlautofilteroperator-enumeration-excel
'OR you can write a quick macro code as shown in Post #2 here
'http://rpaforum.net/threads/blueprism-code-stage-to-paste-data-as-values-in-excel.1190/post-3686
FilteredCount = sheet.AutoFilter.Range.Columns("A").SpecialCells(12).Cells.Count - 1 '12 implies for xlCellTypeVisible
If FilteredCount > 0 then
'This means there were rows returned by the filter
End if
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
varUsedRange = Nothing
End Try
So can anyone check this code is correct or wrong and please provide solution for how to filter three criteria in one column.
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, varUsedRange As Object
Dim FilteredCount as Long
Try
sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
varUsedRange = sheet.UsedRange().address 'you can give your own Range
If sheet.AutoFilterMode Then
sheet.AutoFilterMode = False 'Turn off filters if already applied
End If
sheet.range(varUsedRange).AutoFilter (Field:=[FieldToBeFiltered1], Criteria1:=[FCriteriaToFilter1],Operator:=2, Criteria2:=[FCriteriaToFilter2],Operator:=3, Criteria3:=[FCriteriaToFilter3])
'For text, use within double quotes as Criteria1:="one"
'For multiple conditions in the same column use along with AND or OR operator as per requirement
'sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1, Operator:=xlOr, Criteria2:=2)
'If the Blue Prism Code stage doesn't recognise the Excel constants of xlAnd, xlOr then
'either get the constants from this link
'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlautofilteroperator-enumeration-excel
'OR you can write a quick macro code as shown in Post #2 here
'http://rpaforum.net/threads/blueprism-code-stage-to-paste-data-as-values-in-excel.1190/post-3686
FilteredCount = sheet.AutoFilter.Range.Columns("A").SpecialCells(12).Cells.Count - 1 '12 implies for xlCellTypeVisible
If FilteredCount > 0 then
'This means there were rows returned by the filter
End if
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
varUsedRange = Nothing
End Try