-2

Sample Excel Row range

This above image is from an excel file row data. I want to read each cell from 1 to N cells as array in VBA

dim array() as Variant

I want output as

array(0)= 1
array(1)= West
array(2)= Product 9
array(3)= 15
.
.
.

array(N)= N column
Shyam
  • 649
  • 1
  • 5
  • 20
Siddhu
  • 48
  • 6
  • [Try investigating before asking.](https://stackoverflow.com/a/7651439/11827059) – ENIAC Oct 05 '20 at 06:29
  • @ENIAC that code for columns values into array , i am asking for row values into array – Siddhu Oct 05 '20 at 06:41
  • A double transpose can pull that back into a 1d-array. e.g: `arr = Application.Transpose(Application.Transpose(.Range(.Cells(1, 1), .Cells(1, x)).Value))` where `x` is the last used column – JvdV Oct 05 '20 at 07:24
  • where x is not constant , N number of cells. @JvdV – Siddhu Oct 05 '20 at 07:28
  • `x` can easily be dynamic. Let me show you in a post. – JvdV Oct 05 '20 at 07:30
  • @Siddhu, [there were earlier posts about rows also.](https://stackoverflow.com/a/32674957/11827059) – ENIAC Oct 05 '20 at 18:08

3 Answers3

1

Assuming you want to extract the row 1, here is the code:

Dim array1() As Variant
Dim LastColumn As Long
LastColumn = ActiveSheet.Cells(1, 1).End(xlToRight).Column
ReDim array1(LastColumn - 1)
For LoopVar = 1 To LastColumn
    array1(LoopVar - 1) = Cells(1, LoopVar).Value
Next

To check if your array has the required values, use this code:

For Each cellvalue In array1
    MsgBox (cellvalue)
Next
Shyam
  • 649
  • 1
  • 5
  • 20
  • your code some time executing correctly in some rows. some case **LoopVar** prints **16385**. I have three row Example row2, row5, row7 each row values defined in different array variable. @Shyam – Siddhu Oct 05 '20 at 07:17
1

You can double transpose your values into a 1d-array:

Sub Test()

Dim arr As Variant
Dim x As Long

With ThisWorkbook.Worksheets("Sheet1")
    x = .Cells(1, .Columns.Count).End(xlToLeft).Column
    arr = Application.Transpose(Application.Transpose(.Range(.Cells(1, 1), .Cells(1, x)).Value))
End With

End Sub
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • I modified code for checking , if the row has only one entry then array shows error 'code' Sub Test() Dim arr As Variant Dim x As Long Dim lrow As Long lrow = 11 With ThisWorkbook.Worksheets("REPORT") x = .Cells(lrow, .Columns.Count).End(xlToLeft).Column arr = Application.Transpose(Application.Transpose(.Range(.Cells(lrow, 1), .Cells(lrow, x)).Value)) End With 'MsgBox x For Each cellvalue In arr MsgBox (cellvalue) Next End Sub – Siddhu Oct 05 '20 at 07:43
  • Yes, if a single value is possible than a loop as per the other answer is a fine alternative @Siddhu. – JvdV Oct 05 '20 at 08:01
1

Finally I fixed the issues. Thank you @Shyam i just modified your code little. also I thank all other members who are all helped me.

Sub excelRowRangeToArray()
Dim rowNo As Long
Dim arraycell() As Variant
Dim LastColumn As Long

rowNo = 1 'row number which you want make as one dimensional array
LastColumn = ActiveSheet.Cells(rowNo, Columns.Count).End(xlToLeft).Column
ReDim arraycell(LastColumn - 1)
For i = 1 To LastColumn
    arraycell(i - 1) = Cells(rowNo, i).Value
Next


'To check if your array has the required values, use this code:

For Each cellvalue In arraycell
    MsgBox (cellvalue)

Next

End Sub
Siddhu
  • 48
  • 6