How to read excel file column by column

Question

I want to read excel file column by column. but my code read data row by row.

On Error GoTo errhand
Set ws = oBook.Sheets("Sheet1")
 Set rngSize = ws.UsedRange

 Dim row As Range
        For Each row In rngSize.Rows
            Dim cell As Range
            For Each cell In row.Cells
                MsgBox cell.value
            Next cell
Next row

When you put "For Each row ..." why would you expect columns? — Solar Mike, Feb 26 '23 at 09:50

Storax · Accepted Answer · 2023-02-26T10:20:40.067

As pointed out in the comment Rows will give you the rows. And Columns will give you the columns

Dim col As Range
For Each col In rngSize.Columns
    Dim cell As Range
    For Each cell In col.Cells
        MsgBox cell.Value
    Next cell
Next col

PS I replaced the variable name row by col for better readability

A second way would be to copy the values of the range into an array

Dim vDat As Variant
vDat = rngSize.Value
 
Dim i As Long, j As Long
 
For i = LBound(vDat, 2) To UBound(vDat, 2)
    For j = LBound(vDat, 1) To UBound(vDat, 1)
        MsgBox vDat(j, i)
    Next
Next

In case of an array this version would also be possible

Dim vDat As Variant, element As Variant
vDat = rngSize.Value

For Each element In vDat
    MsgBox element
Next

How to read excel file column by column

1 Answers1