How to read excel file column by column

0 votes

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
Feb 27, 2023 in Others by Kithuzzz
• 38,000 points
359 views

1 answer to this question.

0 votes

Rows will provide you the rows, as mentioned in the comment. And Columns will provide the columns for you.

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

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
answered Mar 18, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
3,768 views
0 votes
1 answer

How to fix a circular reference error by if condition in excel file?

Circular reference in this context refers to ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,600 points
720 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,600 points
2,528 views
0 votes
1 answer

How to read A column in xlsx file with pandas?

You can view all the columns with: excel_data.head() ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,600 points
577 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,212 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,662 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
908 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,053 views
0 votes
1 answer

How can I convert excel to PDF by Libreoffice and keep all format from excel file?

"Times New Roman" typeface does not have ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
1,649 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,600 points
8,046 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP