VBA - AutoFilter by array doesnt work if list only has 1 or 0 rows of data. Big numbers are not filtered too.

[Ashkelon]

To start off, I posted the XL2BB sheets and VBA code below to help better visualize my problem.

Basically, a macro is run each week to refresh the "FilterList" sheet that results in a varying number of rows of data. There could be 0 or more rows each time the macro is run.

From here, I wrote another script to auto-filter the "Database" sheet by the contents of "FilterList". This script mostly works, except for the fact that an error will occur if the list has empty or has only 1 row. Furthermore, big numbers (e.g. row 7 in "FilterList") are not filtered as well. Might have to add that the database might not have the values contained in that list.

Not sure what adjustments are needed for my code or if it needs a complete overhaul.

report.xlsm
	A	B	C	D
1	Order Id	Account No	ISN No	Account Name
2	21400	F000377U	GRP6077025	Company A
3	21404	F000377U	GRP6077045	Company B
4	21405	F000377U	GRP6077050	Company C
5	21410	F000377U	GRP6077075	Company D
6	21559	FR00034U	898709914414013000	Company E
7	21557	FR00034U	898709914414013000	Company F
8	21457	F000559U	898709914414013000	Company G
9	18044	F000559U	898709914414800000	Company H
Database

report.xlsm
	A
1	GRP6077045
2	GRP6077046
3	GRP6077047
4	GRP6077048
5	GRP6077049
6	GRP6077050
7	898709914414013000
FilterList

Public Sub FilterByArray()

Dim count As Integer
Dim list As Variant

Dim Database As Worksheet
Dim Dummy_Sheet As Worksheet

Set Database = ThisWorkbook.Worksheets("Database")
Set Dummy_Sheet = ThisWorkbook.Worksheets("FilterList")

count = WorksheetFunction.CountA(Dummy_Sheet.Range("A1", Dummy_Sheet.Range("A1").End(xlDown)))

'When list has no rows, This next line throws a Run-Time error '13': Type mismatch
'When list only has 1 rows, This next line throws a Run-Time error '1004': Application-defined or object-defined error
list = Split(Join(Application.Transpose(Range(Cells(1, 1), Cells(count, 1)).Value), ","), ",")
 
Database.Range("A4").AutoFilter Field:=3, Criteria1:=list, Operator:=xlFilterValues

End Sub

 

[RoryA]

You should test the count variable before proceeding. If it's 0, don't do anything, if it's 1, just use that as a regular autofilter criterion (not using xlFilterValues), otherwise use your existing code.

 

[Ashkelon]

You should test the count variable before proceeding. If it's 0, don't do anything, if it's 1, just use that as a regular autofilter criterion (not using xlFilterValues), otherwise use your existing code.

Thanks a ton for your help so far, though I'm not sure why the auto filter didn't include the big numbers (e.g. 898709914414013000) after filtering. Weirdly it only occurs if that number is included in one of the rows. If that number is the only row in the list, my code works fine.

count = WorksheetFunction.CountA(Dummy_Sheet.Range("A1", Dummy_Sheet.Range("A1").End(xlDown)))

If count = 0 Then

ElseIf count = 1 Then
Database.Range("A4").AutoFilter Field:=3, Criteria1:=Dummy_Sheet.Range("A1")

Else
list = Split(Join(Application.Transpose(Range(Cells(1, 1), Cells(count, 1)).Value), ","), ",")
 
Database.Range("A4").AutoFilter Field:=3, Criteria1:=list, Operator:=xlFilterValues

End If

End Sub

 

[RoryA]

Cross-posted here: VBA - AutoFilter by array didn't return big numbers as well

How are the numbers stored in each sheet - as numbers, or as text?

 

[Ashkelon]

Cross-posted here: VBA - AutoFilter by array didn't return big numbers as well

How are the numbers stored in each sheet - as numbers, or as text?

The numbers were formated to remove scientific notation (format cells > custom > 0)

 

[Ashkelon]

Question has been answered in the cross-post