[VBA] Populate Array with Range Values applying an IF criteria

Hi all,

I use to work with Arrays, but I populate them in a manual way. This time I need to populate the array with the data (numbers as text) present in a range.

What's my goal? I've a range (Sheets("Values").range("K6:K17")) with some numbers stored as text. These numbers are the names of certain tabs. The array loaded with those names will be the input of a subsequent loop, to work on those selected tabs.

I want to apply an IF condition: if any of the cells in range K6:K17 is empty, discard it and do not load it into the array. In this way, the range K6:K17 has 14 cells and maybe only 4 have data. If I do not load those empty values in myArray, the array will have a dimension of 4 and when I will loop over the myArray I would be able to use the boundaries LBound(myArray) and UBound(myArray).

I've coded the following chain:

Please Login or Register  to view this content.

I obtain the following error:

Please Login or Register  to view this content.

I try to debug the error, and it highlights:

Please Login or Register  to view this content.

Thank you in advance!!!

hi, Eldexoly, the reason for error is that your array has no dimensions. Check the following code as is and then try to comment redim line:

Please Login or Register  to view this content.

For your particular task I would suggest to take the whole range into array:

Please Login or Register  to view this content.

and while looping check if particular array member has a value, example:

Please Login or Register  to view this content.

Hi watersev:

Thank you for your answer! I'll check it right now.

Regarding your solution, I've a question. In the following piece of code you posted:

Please Login or Register  to view this content.

You defined the element of the Array as x(i,1). If my Array has only one dimension, why did you put 2 elements on it?

By the way, I've always defined Arrays in the following way, without specifiend the number of elements:

Please Login or Register  to view this content.

Regards!

try this code and take a note of the array dimensions:

Please Login or Register  to view this content.