vba macro defining a filter based on a array of cells with numbers and text values not working - please, help me

[daviddg]

Hi all,

I'm stuck at the moment with the following problem. I have researching and googling for a solution without luck...

Working with vba excel 2010

I have a workbook with two main tabs: PurchaseReport and CeCos_Tab

The PurchaseReport contains a range of cells where the first column have different elements, including numbers (figures) and texts (strings).

The CeCos_Tab has the list of elemenets (criteria) to be used to filter the former column.

There is an object defined which is: CritList=OFFSET(Criterias,1,0,COUNTA(CeCos_Tab!$A:$A)-1,1); note Criterias is the header of that list: Criterias=CeCos_Tab!$A$1. Assume it's working properly as I have already checked it.

As said the criteria list contains numbers and text values, here is a sample. Criterias: test0, 1, test1, 2, 3, 4, 5, 6, 7 and test2.

​

My code is:

Sub FilterRangeCriteria()
​

Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range

Set wsO = Worksheets("PurchaseReport")
Set wsL = Worksheets("CeCos_Tab")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("CritList")

vCrit = rngCrit.Value

If wsO.FilterMode Then
​

wsO.ShowAllData​

End If

rngOrders.AutoFilter _
Field:=1, _
Criteria1:=Application.Transpose(vCrit), _
Operator:=xlFilterValues​

End Sub​

This way, the filter only presents text (string) values of the criteria list; in my example: test0, test1 and test2.

Meanwhile, if the Criteria1 is "Criteria1:=Split(Join(Application.Transpose(vCrit))), _" the filter only offers the numbers (figures) values of the criteria list; in my example: 1, 2, 3, 4, 5, 6 and 7

How can I get both the texts values and the numbers in my filter criteria: test0, 1, test1, 2, 3, 4, 5, 6, 7, test2

Many thanks in advance! David