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.
VBA Code to store cell range values in an Array variable
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 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 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.
For Example: 1. If the Range is “A1:A5” and Array is strArr then Array will be defined as below:
Excel Cell Range to Array – One Dimensional Array
For Example: 2. If the Range is “A1:B5” and Array is strArr then Array will be defined as below:
Excel Range to Array – Single dimensional array
Conclusion
No matter how many rows and columns are part of the input range you provide, array returned is single dimensional array. This statement in the above function For Each xlCell In xlRange traverse by rows. In other words, first it returns all the column values from left to right for the FIRST ROW then SECOND ROW and so on. This is also very clear from the above pictures.
Note: It is also possible to store them in 2 Dimensional Array, if you want. You need to define a two Dimensional array to store it.
Can we sort Excel Range in Array without writing in excel or pickup one element of array based on the criteria
Hi Prabhat,
We can sort data stored in an Array without writing it in Excel.
Also you can pick up any element of the array by passing the reference number. Kindly Confirm, If it helps.
Thanks,
Vishwa
i want to know how both can be done
Give me your sample requirement and Excel, i will send you the code
I have CSV file sent to me incorrectly
Cell Column should be Day that is numeric and I need converted Need to convert
If
0 = Monday
1=Tuesday
2=Wednesday
3=Thursday
4= Friday
5= Saturday
6 = Sunday
hi, when i select Case ele.classname and im looping through external link to retrieve text elements likes this
—————————–macro1——–
Sub test()
Dim eRow As Long
Dim ele As Object
Set sht = Sheets("Sheet1")
RowCount = 1
sht.Range("A" & RowCount) = "left"
eRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set objIE = CreateObject("internetexplorer.application")
With objIE
.Visible = True
.navigate "http://external-website-link.com"
Do While .Busy Or _
.readyState 4
DoEvents
Loop
Do While .Busy Or _
.readyState 4
DoEvents
Loop
For Each ele In .document.all
Select Case ele.classname
Case "Here is the website HTML-MAIN-CLASS"
RowCount = RowCount + 1
Case "Here is the website HTML-SUB-CLASS"
sht.Range("a" & RowCount) = ele.innertext
—————————————–
my question that its working for text elements, but how can i specify a link elements or picture elements to loop and extract them into my rows.
How to get image elements or image links ?
the above code is not working and its give Run time error 13 & description like Type Mismacth…can you help me for this