Array values becoming Null after Public sub closed

KayPee22 · Feb 6th 2020

Respected Members seeking your inputs

I have public sub where i am storing the values in array and that is called thru main function it picks values rightly and stores it in list but when the Sub ends it does not retain value.

Kindly share your inputs on retaining value

Code

Public Sub Filter()

Set Ranger = worksheet.Range("A2:A" & worksheet.cells(Rows.count, "A").End(xlup).Row)

Ctr = 0

For each cell in Ranger
ReDim Preserve arrlist(ctr)
arrlist(ctr) = cell.text
ctr = ctr + 1
Next

End Sub

'Ctr As long, arrlist() as string, Ranger, cell as Range are declared Public at top

Display More

**rory** · Feb 6th 2020

Which module is the arrlist declaration in, and how are you determining that it is empty after the sub runs?

As a side note, using Redim Preserve is not very efficient and totally unnecessary here as you can simply size the array at the start based on the number of cells in the range.

KayPee22 · Feb 8th 2020

I was able to determine it is empty by putting a watch on the field arrlist, i am still learning its probably how i am using the list, i had used array list in include and exclude function in Macros it is working fine but now when i am using in the loop to get the index values it is empty, its probably the declaration. i am going to try this in variant, i had somehow declared it as string.

awsmitty · Feb 9th 2020

Try

Code

Public Sub Filter()
Dim arrlist() As String
Dim Ranger As Range
Set ws = Sheets("Sheet1")
Set Ranger = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
ctr = 0
For Each cell In Ranger
ReDim Preserve arrlist(ctr)
arrlist(ctr) = cell.Text
ctr = ctr + 1
Next
End Sub

Display More

Array values becoming Null after Public sub closed

Participate now!

Share