Variable Range for Array

[Abgar]

Hi All,

Hoping you can help please. I have the below code, which use an array of data to autofilter another range of data:

Sub CodeSample()


    Worksheets("Pending Raw").Select


    Dim range1 As Range
    Dim var1 As Variant
    Dim sArray() As String
    Dim i As Long
     
    Set range1 = Sheets("Array Data").Range("A3:A13")
    var1 = range1.Value
    
     
    ReDim sArray(1 To UBound(var1))  '// Set the Array for departments
    For i = 1 To (UBound(var1))
        sArray(i) = var1(i, 1)
    Next


    ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:=sArray, Operator:=xlFilterValues

So basically, i have a worksheet with a lot of data (Sheetname "Pending Raw"), which is using an autofilter to capture a list of items in the "Array Data" Worksheet, and then autofilter the "Pending Raw" data accordingly.

This works perfectly. However, im trying to make the array variable, to capture anything entered in column A (i.e. not just A3-A13).
I've tried changing the range line to this:

Set range1 = Sheets("Array Data").Range("A3:A" & Rows.Count).End(xlUp).CurrentRegion

but it pops a "Type Mismatch" error on ReDim sArray(1 To UBound(var1)).

Can anyone help me here please?

Cheers,
ABGar

 

[DanteAmor]

Try this

Set range1 = Sheets("Array Data").Range("A3:A" & Sheets("Array Data").Range("A" & Rows.Count).End(xlUp).Row)

 

[Abgar]

Try this

Set range1 = Sheets("Array Data").Range("A3:A" & Sheets("Array Data").Range("A" & Rows.Count).End(xlUp).Row)

I thought it would be something simple. Thanks Dante

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.

 

[DanteAmor]

I thought it would be something simple. Thanks Dante

Could be

Set range1 = Sheets("Array Data").Range("A3").CurrentRegion

But if you have values ​​above A3 or to the right of A3 they are also included in CurrentRegion.