VBA: Create Array Using Selection

zero269

Active Member
Joined
Jan 16, 2023
Messages
335
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm having some trouble figuring out how to create an array using values from a selection of cells.

One thing to note, is that I'm not storing the values for the selected cells themselves. Depending on who's selecting the cells, it could be any column, so I'm telling Excel to store the values in column A instead, based on the row number for the selected cell... using…
VBA Code:
myArray(i) = Range("A" & cell.Row)
I'm getting a Type Mismatch error for the Array portion, thus preventing me from even storing a single value at this point.
I tried storing the Quiz number into a variable (v2) thinking Long values were the issue, but that resulted in the same error.
VBA Code:
'Create Array from Selection
Sub WIP_Selection_Array()

  'Select visible cells only if selection is > 1
  If Selection.Cells.Count > 1 Then
    Selection.SpecialCells(xlCellTypeVisible).Select
  Else
  End If
  
  'Declarations
  Dim myArray As Variant
  Dim cell As Range
  Dim i As Long: i = 1
  Dim Quiz As Long
  
  'LOOP Selection
  For Each cell In Selection
  
    'v1
    myArray(i) = Range("A" & cell.Row)

    'v2
'    Quiz = Range("A" & cell.Row)
'    myArray(i) = Quiz

    i = i + 1 'index counter
  
  Next cell

End Sub
For example, If I select the four cells in column Q that show "on hold", then my array would be populated with the four associated Quiz numbers in column A.
VBA Testing.xlsm
ABDIJKLOPQ
1QuizTitleAuthorF/NFAR PointsBook LevelWord CountDue DatePickup DateBook Status
214383620,000 Leagues Under the SeaHutchinson, EmilyF2.04.212,22530-Sepon hold
3154A Bear Called PaddingtonBond, MichaelF4.04.724,560
4518852A Christmas CarolHutchison, PatriciaF1.02.86,14730-Sepon hold
541535A Christmas CarolHutchinson, EmilyF2.04.112,16230-Sepon hold
641536A Tale of Two CitiesLorimer, JanetF2.03.812,15230-Sepon hold
Books

Any help would be greatly appreciated…
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Code:
Sub Try_So_Maybe()
Dim ws As Worksheet
Dim colArr
Dim c As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")    '<---- Change required
    For Each c In Selection
        colArr = colArr & "|" & ws.Cells(c.Row, 1).Value
    Next c
colArr = Split(Mid(colArr, 2), "|")
ws.Cells(ws.Rows.Count, 20).End(xlUp).Offset(1).Resize(UBound(colArr) + 1).Value = Application.Transpose(colArr)    '<---- Change required
End Sub
 
Upvote 1
Solution
For example, If I select the four cells in column Q that show "on hold", then my array would be populated with the four associated Quiz numbers in column A.
Try this then

VBA Code:
Sub Fill_Array()
  Dim myArray As Variant
  
  myArray = Evaluate("let(v,textsplit(textjoin(""|"",," & Intersect(Selection.EntireRow, Columns("A")).Address & "),""|""),iferror(--v,v))")
End Sub

Here are my results for the sample data and selection scenario you gave.

1737549976309.png
 
Upvote 1
Thanks jolivanes,

Your code builds an array with the correct values from Column A based on the Row number of the Selection.

1737932044784.png

Code was slimmed down a bit from your original...

VBA Code:
Sub Selection_Array_v1() 'jolivanes

  'Declarations
  Dim colArr, c As Range
  
  'String Selection Values in Col A together
  For Each c In Selection
    colArr = colArr & "|" & ActiveSheet.Cells(c.Row, 1).Value
  Next c
  
  'Split Values into Multi Index Array
  colArr = Split(Mid(colArr, 2), "|")

End Sub

Thank you and best regards,
 
Upvote 0
Hi Peter,

Thanks for another solution to my question. I tested it out as is, and it grabbed the expected values.

Thanks again, Peter. (y)
 
Upvote 0
Thanks for another solution to my question.
You are welcome.
A few comments about the approaches/codes
  1. Clearly one requires looping through each cell of the selection whereas the other grabs all the values at once. Depending on the exact circumstances, each could have its advantages/disadvantages.
  2. Post 2 puts the values into a 0-based array whereas post 3 puts the values into a 1-based array. Either way that should not be critical, but best to be aware of it.
  3. If using the post 2 code there is no need for the "ws" variable or to 'Set' the worksheet. The array is filled in relation to the current selection so it must be on the active sheet since that is where the selection is.
  4. There is also no need to put the relevant values into a string only to then take them out of the string and put them in the array. They can go directly into the array instead.
  5. You did not say what you were going to do with the values once they are in the array** but note that if the values in column A are numerical, the values in the post 2 array will not be numerical but string values. Depending on what you were doing with them subsequently, that may well not matter, but best to be aware of it in case it does matter.
** Depending on what you were going to do with the array once populated (& how big it might be) I might also construct the array differently as well.

So, if you did want to go with a looping approach, I would make the following changes to account for points 3-5 above. (This also makes a 1-based array but could be 0-based if you wanted)

VBA Code:
Sub Fill_Array_Looping()
  Dim myArray As Variant
  Dim c As Range
  Dim i As Long
 
  ReDim myArray(1 To Selection.Count)
  For Each c In Selection
    i = i + 1
    myArray(i) = Range("A" & c.Row).Value
  Next c
End Sub
 
Last edited:
Upvote 1
If "grabbed the expected values" is all you need, you can bypass all the previous suggestions.
This will select what you need.
Code:
Cells(Selection.Cells(1).Row, 1).Resize(Selection.Rows.Count).Select
 
Upvote 0
This will select what you need.
Would it? Did you try it with the example selection given in post 1?
If I select the four cells in column Q that show "on hold", then my array would be populated with the four associated Quiz numbers in column A.

(.. and the question was about filling an array, not making a selection)
 
Upvote 0
Indeed. I just tried it on an example sheet without empty cells/rows.
As far as the array is concerned, pretty simple to adjust. But not needed of course.
Thanks for the rectification Peter.
 
Upvote 0
Hello Peter,
A few comments about the approaches/codes
I really appreciate you taking the time to share this with me.
Clearly one requires looping through each cell of the selection whereas the other grabs all the values at once. Depending on the exact circumstances, each could have its advantages/disadvantages.
Agreed. I think because this is my first time realizing that I 'need' - correction - 'must' start learning how to use Arrays, I've leaned towards a looping option to play around with different datasets. I just happened to start with the more complicated 'Selection' over the 'Range'.

Range will be the most common based on many of my existing macros. I've already got some lined up to use Arrays, even if it's just a few values. I've got some in use that are manual lists, and I want to make them dynamic using Arrays from Ranges.

I also really like the "at once" approach. This is where I should see the best performance. I've got a Table that needs to go through thousands of cells and return Count, MIN, MAX, AVG... as well as some calculations for Student Reading/Testing metrics. I'm using formulas but trying to switch over to a macro-based solution. I've got it working, but I'm currently looping through the Range... which is actually slower than the formulas currently in use. My goal is to grab everything I need to look at (array), then process it in memory before spitting it out to the worksheet.

Or so I 'think' that's the best approach...
Post 2 puts the values into a 0-based array whereas post 3 puts the values into a 1-based array. Either way that should not be critical, but best to be aware of it.
That makes perfect sense. I think this is one aspect that made my earlier attempts at playing with arrays confusing, as I wasn't seeing the results others reported as 'working' for them, but not with my dataset. It's also how I noticed that I was getting an 'Empty' value for index '0'... when I was starting with index '1'.
If using the post 2 code there is no need for the "ws" variable or to 'Set' the worksheet. The array is filled in relation to the current selection so it must be on the active sheet since that is where the selection is.
Excellent point. I'll be sure to check my macros that are using 'Selection' to see if I can clean them up.
There is also no need to put the relevant values into a string only to then take them out of the string and put them in the array. They can go directly into the array instead.
True that. I'm doing something of that nature in a formula using TEXTJOIN. However, I learned there's a limit of 32,767 characters with that function.
Once I get a handle with the arrays, I'll be able to replace the Formula with a macro-based solution... coupled with a 'Worksheet_Change' event.
You did not say what you were going to do with the values once they are in the array** but note that if the values in column A are numerical, the values in the post 2 array will not be numerical but string values. Depending on what you were doing with them subsequently, that may well not matter, but best to be aware of it in case it does matter.
In this particular case, those numbers are just being used to 'find' in another workbook. The macro first gets the 'Column Index' for the 'Book Status' Table column; this value doesn't change. Then, it will look for that Quiz number stored in the array, and once found, the macro returns the 'Row Index' so I can then call on another macro that will update the 'Book Status' based on its current value.

Because I'm experimenting with working between 2 workbooks, instead of one massive one... I came across an issue that when I was looking for those Selection (Quiz number) values in 'Workbook2', only the first one was correct. I noticed the 2nd value was not what I had selected. I later realized that when I 'activated' Workbook2, my selection in 'Workbook1' was no longer valid. This led me to storing those values in an array so they would still be available when I activated Workbook2. Using the array did the trick. (y)
** Depending on what you were going to do with the array once populated (& how big it might be) I might also construct the array differently as well.
For this 'Selection' case, the array would only be a handful of values; books being checked in or out.
So, if you did want to go with a looping approach, I would make the following changes to account for points 3-5 above. (This also makes a 1-based array but could be 0-based if you wanted)
For a Selection, I absolutely prefer a 'looping approach'. Mainly because I feel I can follow how it's working, and tweak things to work in different scenario... especially because I'm only just learning how to work with arrays as a more viable solution.

I just had to add my infamous 'Select visible cells only' line of code, considering my Selection-based macros are usually always in filtered tables.

Your code 'as is' was showing a size of over 3,000 at first, but then I noticed it was adding values that were filtered. :)

1738101646772.png

Love it! (y)
VBA Code:
'https://www.mrexcel.com/board/threads/vba-create-array-using-selection.1268969/post-6244266
Sub Fill_Array_Looping()
 
  'Declarations
  Dim myArray, c As Range, i As Long
 
  'Select visible cells only if selection is > 1
  If Selection.Cells.Count > 1 Then Selection.SpecialCells(xlCellTypeVisible).Select

 'Fill Array with Selection
  ReDim myArray(1 To Selection.Count)
  For Each c In Selection
    i = i + 1
    myArray(i) = Range("A" & c.row).Value
  Next c
 
End Sub

1738101672572.png
 
Upvote 0

Forum statistics

Threads
1,226,462
Messages
6,191,177
Members
453,644
Latest member
karlpravin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top