Excel convert Range to Array in VBA


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Arrays have more powerful options than simple Excel ranges but, happily, it’s very easy to convert a range of cells into an array.

Or sometimes you have a mix of arrays and ranges that need to be managed as a collection of arrays.  That’s what we’ve needed to make the NetworkDays() and NetworkDays_Intl() functions work properly.

Here’s a simple Range2Array function that you can use:

excel convert range to array in vba microsoft office 34996 - Excel convert Range to Array in VBA

Simple on it’s own but very useful when dealing with a mix of arrays and ranges.

There’s no special conversion function necessary, just copy the range variable into an array (Variant) variable.  VBA handles the variable type conversion. If you don’t need the whole function, here’s the main code line for your own VBA:

array = Range 

excel convert range to array in vba microsoft office 34995 - Excel convert Range to Array in VBA

Here’s the code:

 

Public Function Range2Array(Range1 As Range) As Variant

‘ converts an incoming cell range into an array

‘ use with MergeArrays() to include ranges in a merged array

Dim arr1 As Variant

arr1 = Range1

Range2Array = arr1

End Function

Excel now has Dynamic Arrays – Windows, Mac and more …

Dynamic Arrays now in Excel for iPhone and iPad

Bonus for ‘Real Time Excel’ readers

subs profile e1563205311409 - Excel convert Range to Array in VBA
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address