AutoFilter Dynamic Range

[IsraelNunes_Sou]

Hello, 
Im trying to autofilter a Excel Dynamic Range.
If I run the code in a normal table, it works, but when I run the same code to filter a dynamic range I get an error (autofilter failed).

I need to filter the column "D" with two criterias

This is the code I'm using:

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, varUsedRange As Object
Dim FilteredCount as Long

Try

sw = GetWorkbook(handle, workbookname)
ss = GetWorksheet(handle, workbookname, worksheetname)


sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet

varUsedRange = sheet.UsedRange().address 

If sheet.AutoFilterMode Then
sheet.AutoFilterMode = False 
End If

sheet.range(varUsedRange ).AutoFilter (Field:=4, Criteria1:="=611")

FilteredCount = sheet.AutoFilter.Range.Columns("D").SpecialCells(12).Cells.Count - 1 


If FilteredCount > 0 then

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

------------------------------
Israel Nunes Sousa
RPA Developer
Indra
America/Sao_Paulo
------------------------------

[NicholasZejdlik]

What's the difference between the normal table and the dynamic range?

If you're looking to filter by two criteria, try changing the autofilter line to this: sheet.range(varUsedRange ).AutoFilter (Field:=4, Criteria1:="611", Operator:=2, Criteria2:="SecondFilterValue") (2 = xlOr)


------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[IsraelNunes_Sou]

Thanks @Nicholas Zejdlik
That is something I will need too.


About your question:

The normal table its only a sheet with the column name, and the lines,

This is my normal table:

And I use those register to generate the dynamic range.

I think its not working for dynamic range, because I can't put a filter in the first line

The button get inactive

And I can't delete the first line, because its an automatic table, generate by Excel.

I have tried, with a range, from "D2:Until the end" but also didn't work.

I don't know if my case is clear, if not, I can try explain in other way.

I need this so bad. Im trying to do it about 2 weeks, with no success. 

​

------------------------------
Israel Nunes Sousa
RPA Developer
Indra
America/Sao_Paulo
------------------------------

[NicholasZejdlik]

Not entirely sure why it is giving you an error, but you could try doing it without specifying the range. If you select a cell on the header row, AutoFilter should grab everything automatically:

sheet.Range("A2").Select
excel.Selection.AutoFilter Field:=4, Criteria1:="611"​

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[IsraelNunes_Sou]

Didn't work 
Give me the same error "AutoFilter Method of Range Class Failed"

------------------------------
Israel Nunes Sousa
RPA Developer
Indra
America/Sao_Paulo
------------------------------

[NicholasZejdlik · Accepted solution]

On second glance on those tables, the one you're working on looks to be a Pivot Table, which doesn't use AutoFilter. I get the same error that you are getting if I try to use AutoFilter macros on a pivot table.

I'd suggest using the Record Macro feature in Excel to get a look at the specific code you'd need to run to filter the pivot table to your needs. Any VBA macros can be pretty easily imported into Blue Prism code. I believe the code you're looking for is going to be something like this:

sheet.PivotTables(1).PivotFields(4).PivotFilters.Add2 Type:=xlCaptionEquals, Value1:="611"​

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[IsraelNunes_Sou]

OMG! I can't believe, it works!

I only had to make some adjustment to get the field I wanted, and use two criteria.

This is the final code:

Dim ss, ds As Object
Dim excel, sheet As Object
Dim i As Decimal

Try

ss = GetWorksheet(handle, workbookname, worksheetname)


ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet

'sheet.PivotTables(1).PivotFields("Conta do R").PivotFilters.Add (Type:=15, Value1:= "631*"​, Value2:= "611*")

With sheet.PivotTables(1).PivotFields("Conta do R")
For i=1 to .PivotItems.Count
If left(.PivotItems(i).Name, 3) = "631" Or left(.PivotItems(i).Name, 3) = "611" Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
Next i
End With


Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
ss = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
End Try

I tried to insert an ".AutoSort 1" but didnt work.

Im so sorry, its Pivot Table, and I saying wrong all the time,
Luck you discovered !


------------------------------
Israel Nunes Sousa
RPA Developer
Indra
America/Sao_Paulo
------------------------------