How to count no of rows in selected excel file using VB6

Question

I am using below code to read excel file: I want to print only first row data, for that I require to find no of rows in sheet (non blank) .

Set xlBook = GetObject(FilePath)
xlBook.Application.Visible = True
xlBook.Windows(1).Visible = True
xlBook.Application.WindowState = xlMinimized

Dim irow As Integer

For irow = 2 To 101
    MsgBox xlBook.Worksheets(1).Cells(irow, 1).Value
Next

Are you trying to count only used rows (non empty rows) in excel sheet? — Himanshu, Sep 20 '12 at 09:02

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Unfortunately I will not recommend UsedRange.

Two reasons

UsedRange will not give you no of rows in sheet (non blank) as you want it.
UsedRange is highly unreliable. If you want to find the last row then see this link . Please note that this will still not give you number of NON Blank Rows.

To get the number of Non Blank Rows, you will have to use AutoFilter. Use <>"" as the autofilter criteria and then use the visible cells rows count to get the non blank rows.

Edit:

See this link

This post deletes all columns which are empty apart from a specific header. I am sure you can modify it to suit your needs.

How to count no of rows in selected excel file using VB6

1 Answers1