CODEWITHSUNDEEP
arraysvbaloops
Yaahtzeck
Yaahtzeck

Reputation: 227

VBA Looping through selected columns with Cells()

Let's assume I have a spreadsheet with 15 columns ans 20 rows. I would like to make an array out of two columns only (lets say of 2nd and 10th) and all rows. How could I do it?

I suppose this might be a good starting point, but I dont know how to define my selected columns using Cells() funcion.

Dim array1(20, 2) As Single
Set arrays = Sheets("Sheet1")

For i = 1 To 20
For j = 1 To 2
My_array(i - 1, j - 1) = arrays.Cells(i, j - ??)

Upvotes: 0

Views: 349

Answers (2)

codtex
codtex

Reputation: 6548

If I understood you well you want to generate two dimensional array containing all the data for two of sheet columns. I would do such a thing with a custom function looking something like this:

' -----------------------------------------------------------------
' GetMyData - picks data from sheet's first 20 rows and two columns
'    - sheetName {String} - then name of the sheet where the data is taken
'    - col1 {Integer} - first column to read from
'    - col2 {Integer} - second column to read from
' returns: 2-dimensional array containing data of columns from the parameters
Function GetMyData(sheetName As String, col1 As Integer, col2 As Integer) As Variant
    ' declarations
    Dim sheet As Worksheet: Set sheet = ActiveWorkbook.Sheets(sheetName)
    Dim result(20, 2) As Variant

    ' logic
    Dim row As Integer, col As Integer
    For row = 1 To 20
        result(row - 1, 0) = sheet.Cells(row, col1)
        result(row - 1, 1) = sheet.Cells(row, col2)
    Next

    ' return the result
    GetMyData = result
End Function

And you can use it like that:

Private Sub CommandButton1_Click()
    ' get data from Sheet3 first 20 rows columns 2(B) and 10(J)
    Data = GetMyData("Sheet3", 2, 10)
End Sub

Now a couple of words about the function - since we know that we want to get data from two of the columns we can do it in a single loop and do all the job at once here:

For row = 1 To 20
    result(row - 1, 0) = sheet.Cells(row, col1) ' Take data from col1
    result(row - 1, 1) = sheet.Cells(row, col2) ' Take data from col2
Next

Upvotes: 1

fmedv
fmedv

Reputation: 153

Dim array1(20, 2) As Single
Set arrays = Sheets("Sheet1")

For i = 1 To 20
    For j = 1 To 2
        My_array(i - 1, j - 1) = arrays.Cells(i, j)

This should do it

Upvotes: 0

Related Questions