CODEWITHSUNDEEP
excelvba
wDroter
wDroter

Reputation: 1249

Excel VBA array from data

I need to create an array from a list of data in excel. The data will be of different lengths so i will have to find the end of it then create the array. I then need to loop through the array and use each value. I have not used VBA before so anything is helpful. This is what i have been able to get so far:

Sub Calc()
Dim endCell As String      
endCell = Range("B13").End(xlDown).Row    
endCell = "B13:B" + endCell    
Dim nums As Variant

nums = Range(endCell)

End Sub

Upvotes: 2

Views: 6012

Answers (4)

phoog
phoog

Reputation: 43036

You won't have to do that. Just do this:

Dim varValues() as Variant 

' ...

varValues = Range(endCell).Value

For a range object that comprises more than one cell, the value property will return an array of values.

If you don't know whether the range has more than one cell, but you want to have an array of values regardless, you could write a function to achieve that (thanks to brettdj for the inspiration):

Function GetValue(rng As Range) As Variant()
    Dim varResult() As Variant

    If rng.Cells.Count = 1 Then
        ReDim varResult(0 To 0) 'I prefer to be explicit about the base
        varResult(0) = rng.Value
    Else
        varResult = rng.Value
    End If

    GetValue = varResult
End Function

Upvotes: 4

mooseman
mooseman

Reputation: 2017

I am not sure if this is any better or cleaner.

Sub Calc()
Dim endCell As String

endCell = Range("B13").End(xlDown).Row
endCell = "B13:B" & endCell
Range(endCell).select

For each c in selection
   use any attribute of the cells one at a time like, c.value c.formula.....
Next c
End Sub

Upvotes: 0

assylias
assylias

Reputation: 328568

Starting from your code, you could loop over the array in the following way:

Sub Calc()
    Dim endCell As String

    endCell = Range("B13").End(xlDown).Row
    endCell = "B13:B" & endCell

    Dim nums As Variant
    Dim i As Long

    nums = Range(endCell)

    For i = LBound(nums,1) To UBound(nums,1)
        'do something with nums(i,1)
    Next i
End Sub

Upvotes: 0

Francis P
Francis P

Reputation: 13655

use the Transpose function:

nums = Application.WorksheetFunction.Transpose(Range(endCell).value)

Upvotes: 1

Related Questions