CODEWITHSUNDEEP
excelvba
Valwrie
Valwrie

Reputation: 133

How do I obtain a dynamic array and what do i dim it as? VBA

I have recently started using arrays and have been very confused as to how it all works. The current array (subject to change) starts at D4. How do I go about loading the array correctly as I can't seem to use the array list to filter later on?

Any help would be greatly appreciated, thank you in advance!

   D
4 May
5 Ann
6 June


Dim wb As Workbook
Dim ws As Worksheet
Dim arrlist() As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("WorkingSheet")

lastrow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
numr = lastrow - 3

ReDim arrlist(0, 1 to numr)

ws.Range("D4:D" & lastrow).Select

For c = 1 To numr
    arrlist(0, c) = CStr(Selection(c, 1).Value) 
Next

Upvotes: 0

Views: 64

Answers (1)

Mikku
Mikku

Reputation: 6654

You can use Range.Value to set the Array.

Change your code to this Simple version.

Dim wb As Workbook
Dim ws As Worksheet
Dim arrlist() As Variant

Set wb = ThisWorkbook
Set ws = wb.Worksheets("WorkingSheet")

lastrow = ws.Cells(ws.Rows.Count, "D").End(xlUp).row

arrlist = ws.Range("D4:D" & lastrow).Value

'Additional Code
ReDim arr(1 To UBound(arrlist)) As Variant
For i = LBound(arrlist) To UBound(arrlist)
    arr(i) = arrlist(i, 1)
Next

wsd.Range($A$2:$AB$2260).AutoFilter Field:=2, Criteria1:=arr, Operator:=xlFilterValues

#NOTE: This will give you a 2-D array, so to print all the values from it, use

For i = Lbound(arrlist) to Ubound(arrlist)
    Debug.Print arrlist(i,1)
Next

Just make sure to refer any element using arrlist(i,1) that 1 is important, otherwise you will get an error.

Upvotes: 1

Related Questions