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
– Himanshu, Commented Sep 20, 2012 at 9:02

Community · Accepted Answer · 2017-05-23 11:49:08Z

1

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.

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Sep 20, 2012 at 9:33

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related