topic RE: AutoFilter Dynamic Range in Product Forum

AutoFilter Dynamic Range

IsraelNunes_Sou — Thu, 19 Nov 2020 02:04:00 GMT

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

RE: AutoFilter Dynamic Range

NicholasZejdlik — Thu, 19 Nov 2020 15:33:00 GMT

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

RE: AutoFilter Dynamic Range

IsraelNunes_Sou — Thu, 19 Nov 2020 16:20:00 GMT

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

RE: AutoFilter Dynamic Range

NicholasZejdlik — Thu, 19 Nov 2020 17:09:00 GMT

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

RE: AutoFilter Dynamic Range

IsraelNunes_Sou — Thu, 19 Nov 2020 20:25:00 GMT

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

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

RE: AutoFilter Dynamic Range

NicholasZejdlik — Fri, 20 Nov 2020 00:31:00 GMT

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

RE: AutoFilter Dynamic Range

IsraelNunes_Sou — Fri, 20 Nov 2020 01:50:00 GMT

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