Assigning an Array Using Index/Named Ranges

[bookiiemonster]

Hi!

I have a workbook where I need to refer to specific columns, so I've named them, and then I need to build arrays that need to be dynamic (as in, the amount of values in the columns will change). In all scenarios, each array will just be one column.

Dim numOfRows As Integer
numOfRows = Application.WorksheetFunction.CountA(Range("MyNamedRange"))
Dim MyNamedRangeArray() As Variant
MyNamedRangeArray = Range("Index(MyNamedRange,1):Index(MyNamedRange," & numOfRows & ")")

The MyNamedRange is Column A in this sheet. Attaching a screenshot of the range and the name set up.

If I do

Range("Index(MyNamedRange,1):Index(MyNamedRange," & numOfRows & ")").Select

It highlights the correct cells, so I know this Range is written correctly (and I use Range with Index for Named Ranges all the time).

No matter what I do (like add .Value or Value2 to the end of the Range, use Transpose, ect) I can't get MyNamedRangeArray to fill with the values of that Range. I've ran code that prints out the values of an Array or counted the number of values in an Array, and if I blunt force fill the Array, then those codes spit out the values. I really need this to be dynamic because more values will get added or removed from this column (there will always be quite a few, I want to say never less than 10, my example is just using a couple for testing purposes). If there's another way to easily make an Array dynamic in size, happy to rethink my strategy.

Any and all advice is greatly appreciated, as this is my first time using Arrays in VBA (I'm relatively new to VBA coding in general). Hoping I'm just missing something basic.

Thank you!!

 

[Artik]

I will admit that I was not familiar with this method of referring to a range of cells. I checked, in my case it works, that is, MyNamedRangeArray is filled with values.
However, since this way does not work for you, you can take a more classical approach:

Dim numOfRows As Long
Dim MyNamedRangeArray() As Variant

numOfRows = Application.CountA(Application.Range("MyNamedRange"))
MyNamedRangeArray = Application.Range("MyNamedRange")(1).Resize(numOfRows).Value

Artik

 

[kevin9999]

Your method of filling an array is a tad unorthodox. Why make a column a named range when it already has a name (Column A in your case). I would normally do something like:

Option Explicit
Sub Fill_Array()
    Dim ws As Worksheet, MyNamedRangeArray
    Set ws = Worksheets("Unbundling")
    MyNamedRangeArray = ws.Range("A1", ws.Cells(Rows.Count, "A").End(xlUp))
    
    'to insert the values back to the sheet use something like
    Range("C1").Resize(UBound(MyNamedRangeArray), 1).Value = MyNamedRangeArray
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!
A very slightly different approach to post 2 but this would not miss any values if there happened to be any blank cells amongst the actual data in the named range.

Sub Fill_Ary()
  Dim MyNamedRangeArray() As Variant
 
  With Range("MyNamedRange")
    MyNamedRangeArray() = .Resize(.Cells(.Count).End(xlUp).Row).Value
  End With
End Sub

 

[Peter_SSs]

BTW, I forgot to add in my previous post that (like @Artik mentioned) your original code in post 1 works for me as-is (provided no blank cells among the data that is in the named range).
Below is my array filled at the end of the code.
I'm wondering what, if anything, ends up in the array when you run your code with that sample data?

 

[bookiiemonster]

BTW, I forgot to add in my previous post that your original code in post 1 works for me as-is (provided no blank cells among the data that is in the named range).
Here is my array filled at the end of the code

View attachment 121490

I'm wondering what, if anything, ends up in the array when you run your code with that sample data?

So interesting! I was trying to figure out how to see something like this, so far I was using other code people wrote. How can I get this view?

One of the reasons I know my code isn't working, at least for me, is because I then feed that array into different code to filter a table. No matter what I try, the table won't filter. That's when I did a manual fill of the array values as a check, to make sure it wasn't the filtering code that was breaking, but it worked then. And when I try to run any code that references an array, it'll error out if I use my code as is, but will work fine again with manually filling in values.

I'm going to try the ideas posted in the thread tomorrow morning and I'll report back on if anything works. What's been posted seems a lot more elegant and easier to read, so if it works I'll just use that going forward.

Thank you!

 

[Peter_SSs]

How can I get this view?

In the vba window use the menu 'View -> Locals' and step through your code one line at a time with F8

because I then feed that array into different code to filter a table. No matter what I try, the table won't filter.

.. so there is likely something wrong with the other code or how you are passing the array to it.
Perhaps you could show us the code that does all of that and explain exactly what the code should be doing?
Might also need details of the table it is supposed to be filtering.

 

[bookiiemonster]

In the vba window use the menu 'View -> Locals' and step through your code one line at a time with F8


.. so there is likely something wrong with the other code or how you are passing the array to it.
Perhaps you could show us the code that does all of that and explain exactly what the code should be doing?
Might also need details of the table it is supposed to be filtering.

Thank you! I'll first use this view to see if my array is filling and use that information to determine next steps and what to try.

I can't share the full file because it's for work and I'm building onto a workbook with a lot of macros and moving parts, BUT, I can probably isolate the moving parts for this specific issue and throw that into it's own workbook to share.

 

[Peter_SSs]

I'll first use this view to see if my array is filling and use that information to determine next steps and what to try.

Good idea. Good luck.

I suspect it may be that you other code is expecting a 1-dimensional array rather than a 2-dimensional array that the above code is currently producing. If that is the case then you could use a structure like this to produce the 1-D array

Sub Fill_Ary_v2()
  Dim MyNamedRangeArray() As Variant
  
  With Range("MyNamedRange")
    MyNamedRangeArray() = Application.Transpose(.Resize(.Cells(.Count).End(xlUp).Row).Value)
  End With
End Sub

 

[bookiiemonster]

Good idea. Good luck.

I suspect it may be that you other code is expecting a 1-dimensional array rather than a 2-dimensional array that the above code is currently producing. If that is the case then you could use a structure like this to produce the 1-D array

Sub Fill_Ary_v2()
  Dim MyNamedRangeArray() As Variant
 
  With Range("MyNamedRange")
    MyNamedRangeArray() = Application.Transpose(.Resize(.Cells(.Count).End(xlUp).Row).Value)
  End With
End Sub

View attachment 121493

This worked!! You were right, my original code was actually working correctly, but it was making a 2-D array, so the code it fed into wasn't working.

I used our code as written above and it's now working flawlessly. This is also a lot cleaner and easier to use then how my code was written with the Indexes, so I'm honestly just going to re-purpose this going forward.

"'View -> Locals' and step through your code one line at a time with F8" is exactly what I was missing to be able to troubleshoot effectively, so thank you so much for this (the problems with being completely self-taught haha).

You saved me friend, thank you!!!