How to get Excel Range in Array
Many a times while doing coding in excel, we need all the values stored in a Cell Range in an Array format. So that during any point of time we can access the value from the array, rather going in Excel Cell and reading from there.
Thinking that in mind, I just created a Function (UDF – User Defined Function) which will accept Cell Range as Input and Return a String Array having all the values in that cell Range.
Step 1. Copy and Paste the below code in Excel Macro Module
Public Function GetArray(xlRange As Range) As String() Dim strArray() As String Dim iCounter As Integer Dim intCount As Integer Dim xlCell As Range iCounter = 0 intCount = xlRange.Cells.Count ReDim strArray(0 To intCount - 1) For Each xlCell In xlRange strArray(iCounter) = xlCell.Value iCounter = iCounter + 1 Next GetArray = strArray End Function
Step 2. You can use the above Function anywhere in the Excel macro and it will return the String Array.
Sub GetArray() Dim strArr() As String strArr() = GetArray(ActiveSheet.Range("A1:A5")) End Sub
I am storing All the values in a Single Dimentional Array. Therefore If you are passing Range of Single Row or a Single Column then all the Values will be Stored as a single array.
For Example: 1. If the Range is “A1:A5″ and Array is strArr then Array will be defined as below:
For Example: 2. If the Range is “A1:B5″ and Array is strArr then Array will be defined as below:
If you want the workbook with this Function Mail Me
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box