asked on

Access 2016 Determine Number Of Rows In Excel Spreadsheet

Hi Experts,

I have some VBA Code that reads data in an Excel Spreadsheet, validates the data and writes it to an Access Table.

I want to implement a Progress Bar (I know how to do it) but I need to know up front how many rows are in the Spreadsheet in order to show progression.

Is there a way to do that and if so what is it?

Thanks,
Bob C.

Norie

Bob

Can you post the code that reads the data from Excel?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel Pineault

lLastRow = oExcelWrkSht.Cells(oExcelWrkSht.Rows.Count, 1).End(xlUp).Row

Open in new window

where oExcelWrkSht is your Excel Worksheet object

Set oExcelWrSht = oExcelWrkBk.Sheets("YourSheetName")

Open in new window

Martin Liss

Danial, since the "1" in your code line refers to column 'A', it will only give the expected results if column 'A' is the longest, or at least the target, column.

Daniel Pineault

Indeed, you need to adjust 1 to the column of interest.

Martin Liss

I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Distinguished Expert in Excel 2018
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
       Experts Exchange Top Expert VBA 2018 to 2020

Dale Fye

You can also use the "UsedRange" property of the worksheet:

intRowCount = oSHT.UsedRange.Rows.Count

Bob Collison

ASKER

Thanks Dale.

Martin Liss

Bob, I don't about Access, but Excel itself does not keep good track of UsedRange and so you are better off using my or Daniel's method.

Bob Collison

ASKER

Hi Martin,

Thanks for the insight.

Bob C.

Dale Fye

I did not know that, Martin, thanks for the post.

Dale

Martin Liss

You're welcome. In case anyone doesn't know, you can find what Excel thinks is the end (lower right-hand corner) by pressing Ctrl+End.