{"id":12139,"date":"2012-06-07T11:57:18","date_gmt":"2012-06-07T11:57:18","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1737"},"modified":"2022-08-06T13:14:28","modified_gmt":"2022-08-06T13:14:28","slug":"excel-range-in-array","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/06\/excel-range-in-array\/","title":{"rendered":"How to get Excel Range in Array"},"content":{"rendered":"
Many times while developing an application in Excel VBA, we need all the values stored in an excel Cell Range in an Array variable. So that at any point in time we can access the value from the array, rather than going to Excel Cell and reading it from there. Also, now that you have all the values from a cell range in an array, it is very easy to iterate them and perform any kind of action we want on them.<\/p>\n
This is one of the functions I always keep handy. I am sharing it with you guys so that you don’t have to write such functions every time you are working on any excel VBA project. The following function will accept Cell Range as Input and Return a String Array<\/strong> having all the values in that cell Range. <\/p>\n Step 1.<\/strong> Copy and Paste the below code in Excel Macro Module<\/p>\n \nStep 2.<\/strong> You can use the above Function anywhere in the Excel macro and it will return the String Array.<\/p>\n <\/p>\n I am storing All the values in a Single Dimensional 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.\r\n\r\nPublic Function GetArray(xlRange As Range) As String()\r\n Dim strArray() As String\r\n Dim iCounter As Integer\r\n Dim intCount As Integer\r\n Dim xlCell As Range\r\n \r\n iCounter = 0\r\n intCount = xlRange.Cells.Count\r\n \r\n ReDim strArray(0 To intCount - 1)\r\n For Each xlCell In xlRange\r\n strArray(iCounter) = xlCell.Value\r\n iCounter = iCounter + 1\r\n Next\r\n \r\n GetArray = strArray\r\n \r\nEnd Function\r\n\r\n<\/code><\/pre>\n
\r\nSub GetArray()\r\n Dim strArr() As String\r\n strArr() = GetArray(ActiveSheet.Range(\"A1:A5\"))\r\nEnd Sub\r\n<\/code><\/pre>\n
\n
For Example: 1. <\/strong>If the Range is “A1:A5”<\/strong> and Array is strArr then Array will be defined as below:
\n<\/p>\n