Access Column In Array VBA

Question

My code begins with assigning the sheet and grabbing the row count

Set ws2 = ThisWorkbook.Worksheets("Sheet2")
lastRow22 As Long
lastRow22 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

I have the following which put the entire column into an array from column 1 to 80

Dim arrEntireWs2() as Variant  
With ws2
    arrEntireWs2 = .Range(.Cells(2,1),.Cells(lastRow22,80)).Value
End With

Then I loop through it

Dim lngArrEntireWs2Index as Long
For lngArrEntireWs2Index = LBound(arrEntireWs2,1) to Ubound(arrEntireWs2,1)
    'Things I want to do
Next lngArrEntireWs2Index

My question is how do I grab the value at a certain column on the row that it is looping through? Like how would I grab what is on column 10 while going through the loop?

Doug Coats · Accepted Answer · 2017-07-31 18:54:40Z

1

This is what youre looking for....

Dim lngArrEntireWs2Index as Long
For lngArrEntireWs2Index = LBound(arrEntireWs2,1) to Ubound(arrEntireWs2,1)
     If arrEntireWs2(lngArrEntireWs2Index, 10) Then
          debug.print; arrEntireWs2(lngArrEntireWs2Index, 10)
     End if
Next lngArrEntireWs2Index

this looks better to me

Dim ws2 as workbook
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

Dim arr2 as Variant  
With ws2
    arr2 = .UsedRange
End With

Dim i as Long
For i= LBound(arr2,1) to Ubound(arr2,1)
    If arr2(i, 11) Then
        debug.print; arr2(i, 11)
    End if
Next i

edited Jul 31, 2017 at 18:54

answered Jul 31, 2017 at 18:48

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doug Coats Over a year ago

FYI the naming conventions are kinda clunky and make for difficult readibility

Collectives™ on Stack Overflow

Access Column In Array VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related