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

Ashkelon

New Member
Joined
Dec 19, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1Order IdAccount NoISN NoAccount Name
221400F000377UGRP6077025Company A
321404F000377UGRP6077045Company B
421405F000377UGRP6077050Company C
521410F000377UGRP6077075Company D
621559FR00034U898709914414013000Company E
721557FR00034U898709914414013000Company F
821457F000559U898709914414013000Company G
918044F000559U898709914414800000Company H
Database
report.xlsm
A
1GRP6077045
2GRP6077046
3GRP6077047
4GRP6077048
5GRP6077049
6GRP6077050
7898709914414013000
FilterList

VBA Code:
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,465
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Dec 19, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Ashkelon

New Member
Joined
Dec 19, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Question has been answered in the cross-post
 

Watch MrExcel Video

Forum statistics

Threads
1,127,663
Messages
5,626,163
Members
416,166
Latest member
Archimed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top