find fast eof in excel

Question

  Set xlWrkBk = GetObject(str_File)

  Set xlsht = xlWrkBk.Worksheets(1)

'  rng = xlsht.UsedRange.Rows.Count

'  rng = xlsht.Cells(xlsht.Rows.Count, "A").End(xlUp).Row

'  rng = xlsht.Cells.SpecialCells(xlCellTypeLastCell).Row

  rng = xlsht.Cells.SpecialCells(xlLastCell).Row

it all works slow

is there a faster way?

Answer 1

Hi,

What do You mean with slow? How big is the file (file size) and how large area of the worksheet is used?

---

Kind regards, Dennis (http://xldennis.wordpress.com/)

Answer 2

maybe slow in open workbook , not return count of  the EOF.

so change a way to access the object

---

Laddie

Answer 3

What I would suggest is to limit the number of rows to investigate. If You know that the number of rows used will never exceed, for instance 200000 rows, then You can use the following approach:

Dim xlSheet As Excel.Worksheet

Dim lnLastRow As Integer

Set xlSheet = ActiveSheet

lnLastRow = xlSheet.Range("A250000").End(xlUp).Row

 

---

Kind regards, Dennis (http://xldennis.wordpress.com/)

Answer 4

Hello HPLvM:

Here's another method that uses the "UsedRange" property.  This method has never failed me, and it avoids having to search up thousands of empty cells to find the last row with data.

 

Option Explicit
Public Sub Test()
Dim lngLastRow As Long
lngLastRow = ActiveSheet.UsedRange.Rows.Count
lngLastRow = lngLastRow + ActiveSheet.UsedRange.Row - 1
MsgBox ("Last Row = " & lngLastRow)
End Sub

 

The first computation of lngLastRow gives the number of rows in a "used range".  For example, if you start your data in row 10 (rows 1-9 are empty) and end your data in row 14, then the number of used rows is 5.  Of course, this is not the answer you want.  So the next statement finds the first non-empty row in the range.  In this example, the first non-empty row would be 10.  It subtracts 1 from 10, giving 9 rows that must be added to achieve the ACTUAL last row, which is 9 + 5 = 14.

You can use the debugger to see how these properties are rendered.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 5

Rich,

The UsedRange is not always reliable. Charles William's has covered it well in several articles at his blog http://fastexcel.wordpress.com/.

It's very easy to verify it... Keep in mind that to find the last cell with data and to find the last used cell per se does not need to be the same cell. Obviously the OP wants to find the last cell with data.

---

Kind regards, Dennis (http://xldennis.wordpress.com/)

Answer 6

Dennis:

Thanks for the information.  I haven't run into any issues with it, but I will review the link you sent me.  This method is still recommended by one of the foremost authorities on Excel (that doesn't mean he's always correct), John Walkenbach, who has sold more Excel books than any other human being.  The reference to the UsedRange method is in his 2000, 2003 and 2007 books (Power Programming with VBA).  In the Excel 2007 version, it is on page 332.  But... being an open minded scientist that uses the empirical method, I will check out your references.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 7

Rich,

You're welcome but please keep in mind that it exist an OP so I don't want to hijack this thread.

I have no intention to discuss Walkenbach's books in public at all. The reason to it is quite simple, I'm an author myself.

Glad to read that You are an open minded scientist.

But why do You need to dump all of Mr Walkenbach's books on my head?

Please keep it on a reasonable level. After all, all we want is to solve another guy's issue, right?

---

Kind regards, Dennis (http://xldennis.wordpress.com/)

Answer 8

Hi HPLvM,

Could you please provide more information? On which code line does it work slow? As Dennis asked, is the file size very big? Would you mind providing the whole project for us to reproduce?

Have a nice day.

Best regards

---

Liliane Teng [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 9

HPLvM,

Take your pick :)

To find the last row which has data use this.

Sheets("Sheet1").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

To find the last Column which has data use this.

Sheets("Sheet1").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column

To find the last cell which has data use this.

Sheets("Sheet1").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Address

Note: Replace Sheet1 with the relevant sheetname.

Hope this helps...

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 10

Have a look at this as well:

Dim usedRowCount as Integer= workSheet.Rows.SpecialCells(XlCellType.xlCellTypeLastCell, XlSpecialCellsValue.xlTextValues).Row
Dim usedColCount as Integer= workSheet.Rows.SpecialCells(XlCellType.xlCellTypeLastCell, XlSpecialCellsValue.xlTextValues).Column

Loop through these counts and read data if available in cells.