Building an Array in VBA from range

[johnbird1988]

Hello,

I need some help with building an array for a auto filter. At the moment it is not filtering / handling the value correctly. My code I have at the moment is below:

Sub Test()
Dim nDates as Variant

Set rng = .Range("A2:B" & nRows)

nDates = Sheet1.Range("L2:M8").Value

    .AutoFilterMode = False

With rng
        .AutoFilter Field:=1, Criteria1:=nDates, Operator:=xlFilterValues
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    .AutoFilterMode = False


End Sub

The problem is because I think Excel handles the data in Sheet1.Range("L2:M8").Value differently. Column L contains a list of financial years like "2016/2017, 2017/2018) and Column M is whole years like (2015, 2016). If I manually add these values into an array like Array("2015", "2016", "2016/2017", "2017/2018") this work find. I was just wondering what I am doing wrong or if there is another way I should be building my array.

Thank for our help,

Cheers,

John

 

[Norie]

John


As it is the code is pulling both columns and putting them in a 2 dimensional array, for the filter you would need a 1 dimensional array.

Which values do you want in the array?

Is it the dates from column L or the years from column M?

 

[johnbird1988]

Hello Norie,

I would like to include both column into the array if possible.