adding columns to an arrray

  • Thread starter Thread starter David Macdonald
  • Start date Start date
D

David Macdonald

How to I load into an array values from a worksheet that are not in a
contiguous range?
For example a 3 column array made up of (from the same row): value in F, sum
of D - G, value in S
 
You could loop through the rows and build your array.
You could pick up each non-contiguous column and build a multi-column array in
your code.
You could pick up the entire range at once and then just ignore the columns you
don't need.

Or you could even pick up each non-contiguous column and use 3 different arrays,
but loop through them in sync.

Here's an example of the first:

Option Explicit
Sub testme()
Dim myArr() As Variant
Dim HowMany As Long
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim rCtr As Long

Set wks = Worksheets("Sheet1")

With wks
'building the array based on column F
Set myRng = .Range("F1", .Cells(.Rows.Count, "F").End(xlUp))
End With

HowMany = myRng.Cells.Count

'how many rows by 3 columns
ReDim myArr(1 To HowMany, 1 To 3)

rCtr = 0
For Each myCell In myRng.Cells
rCtr = rCtr + 1
myArr(rCtr, 1) = myCell.Value
myArr(rCtr, 2) = Application.Sum(myCell.EntireRow.Range("D1:g1"))
myArr(rCtr, 3) = myCell.EntireRow.Range("s1")
Next myCell

'just to show it works
For rCtr = LBound(myArr, 1) To UBound(myArr, 1)
MsgBox myArr(rCtr, 1) & vbLf & myArr(rCtr, 2) & vbLf & myArr(rCtr, 3)
Next rCtr

End Sub

I used Application.Sum(Dx:Gx) to determine the sum. That includes column F. So
if F contains a number, it'll be included in the sum.
 
Back
Top