Variable Range for Array

Abgar

Active Member
Joined
Jun 20, 2009
Messages
253
Hi All,

Hoping you can help please. I have the below code, which use an array of data to autofilter another range of data:
Code:
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:
Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
Try this

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
Im glad to help you. Thanks for the feedback.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
I thought it would be something simple. Thanks Dante :)
Could be

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

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top