Excel end of file 2

[gwgeller]

Hello, I have multiple excel spreadsheets that have varying numbers of rows. Is there a way to find the last row of a spreadsheet(that holds data)? I need to exit a loop when I get to the last row, not when I get to the end of the file. Right now I have to input special text in the last row and check for it in my loop. I was think worksheets(1).rows().count, but that gives the max rows, not how many are actually holding data or the last row that holds valid data. Any help is appreciated, thanks.

 

[xlbo]

See this luverly little FAQ
faq707-2115

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[gwgeller]

Awesome, thanks.

 

[SBendBuckeye]

Geoff,

How'd you get your smiley faces to do that? Thanks!

Have a great day!

j2consulting@yahoo.com

 

[TheBitDoctor]

Geoff:

There are some caveats that should be noted about your FAQ:

First, the ‘UsedRange’ function returns just that, the area of the sheet that is in use. So, if you open a new sheet and only place data in ‘C3’ and ‘E5’ then your FindLastRow routines return 3 instead of 5. To return the row number of the last row in the used range you need to modify your code to this:

r = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

Second, the UsedRange and Cells.SpecialCells(xlCellTypeLastCell) functions suffer from the same design choice within Excel. Excel considers any cell that it is tracking information for as being ‘used’. Therefore, if you unlock a cell, change its format, or enter data and then clear the cell, Excel will include that cell within the areas considered by these functions. A good discussion on this issue and ranges in general is given at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexvba/html/odc_5709_chap05idx.asp

This discussion also gives Microsoft’s function to find the last real cell containing data:

Sub GetRealLastCell()
Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Range("A1&quot.Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1&quot, xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = Cells.Find ("*",Range("A1&quot, xlFormulas, , xlByColumns, xlPrevious).Column
Cells(lRealLastRow, lRealLastColumn).Select
End Sub

Regards,
Ron

 

[xlbo]

Sorry to disagree but it returns 5 for me and the whole point of the FAQ was to address the deficiencies with the UsedRange function - hence the title of the FAQ.
SBend - it's one of the emoticons in the list that TT uses

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[gsgriffin]

I was having trouble finding the last row in my sheet. It kept giving a row around 8000 when I had only 600. I used the Geoff's techniques, and none of them work in my situation. The BitDoctors solution got it the first time.

Thanks BD

 

[xlbo]

The reason mine doesn't have the find method is that there is another FAQ by Lommah which covers that - I may have to put a link to it in my FAQ though

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244