Detecting last data row in Excel.Workbook Object

[Mindcrime]

I am reading in data from an Excel Workbook into an Access Database in Access.

How can I detect what is the last filled row of the Spreadsheet?

For example if the spreadsheet only has two rows populated, how can I stop the code from running down all 65000 rows?

[Spajeoly]

What I do is I run a loop, at the beginning of the loop I check to see if there is anything in the current cell, if there isn't, I exit the function. Here's an example:

For i = 3 To 1000 'or whever your max number will ever be

If Range("A" & i).FormulaR1C1 = "" Then
Range("A1").Select
Exit Sub
End If

'Put your code here

Next i

Maybe that aint the best way to do it but I was unable to find how to identify the last one with data in it.

This code works for me perfectly since if there is no text in the cell, I do not need it or anything after it.

Hope this helps.

[shivaraj]

The below will give you the address of the last used cell.

Private Function FindLastCell() As String
Dim LastColumn As Integer
Dim LastRow As Long
Dim FirstColumn As Integer
Dim FirstRow As Long
Dim LastCell As Range
Dim FirstCell As Range
Dim strAddrRng As String

If objExcel.WorksheetFunction.CountA(objExcel.Cells) > 0 Then
Set FirstCell = objExcel.Cells(1, "A")
strAddrRng = FirstCell.Address
'Search for any entry, by searching backwards by Rows.
LastRow = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _
searchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _
searchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Set LastCell = objExcel.Cells(LastRow, LastColumn)
strAddrRng = strAddrRng & ":" & LastCell.Address
End If
FindLastCell = strAddrRng
Set FirstCell = Nothing
Set LastCell = Nothing
End Function


- cheers
Shivaraj

[Frans C]

Or use something like:
MySheet.UsedRange.Rows.Count

[alex_read]

Far easier way is to use this one:

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

[Mindcrime]

Thanks Alex_read that worked great.

Cheers

[Spajeoly]

Right on guys, that helps a bunch, thanx a lot.

[Mindcrime]

MaxRowNumber = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

I can only seem to use this code only once during runtime, the second time I get a message 'Object Variable or With Block Variable not set'.

I am setting my ExcelApp object to nothing each time and closing the opened Workbook.

Good old Microsoft... something somewhere isn't being set to nothing!

I am using Excel 9.0 Object Library.

[opus]

Hi Mindcrime

this feature doesn't even work correctly when using it from standard EXCEL menu!

BTW
There is a new FORUM "VBA" , please use this one for questions like this!

[alex_read]

I am using Excel 9.0 Object Library.

The activesheet method is a property of the Excel Application variable, sso you need to specify this:

Dim xyz As Excel.Application
Set xyz = New Excel.Application

xyz.Workbooks.Add
xyz.Workbooks(1).Worksheets(1).Select

Msgbox xyz.Activesheet.range("A1").value

[alex_read]

ha, okay just realised that would create a new workbook & that cell A1 would always be blank to start with, so you'll get a blank messagebox there - even so, it illustrates what I'm on about!

If this is still causing you grief, you can call on a variable holding a single worksheet in the collection:

Dim xyzsheet as Excel.Worksheet
Set xyzsheet = xyz.Workbooks(1).Worksheets(1)

xyzsheet.Select
Msgbox xyzsheet.Range("A1").value

[Mindcrime]

I thought you were trying to teach my granny to suck eggs then!

[Frans C]

Originally posted by opus
BTW
There is a new FORUM "VBA" , please use this one for questions like this!

Where do you draw the line between Visual basic questions and VBA questions?
If you read an excel file from a visual basic executable, is it Visual basic or VBA?
If this is VBA, then is a question about the DateSerial, Mid$ or Round function a VBA question?
These functions are defined in the VBA library, so strictly speaking these questions should be asked in the VBA forum.

I don't think we should be too strict about this.

[Mindcrime]

Hi OPUS

How did you get to be a MODERATOR?

Originally posted by opus
Hi Mindcrime

this feature doesn't even work correctly when using it from standard EXCEL menu!

BTW
There is a new FORUM "VBA" , please use this one for questions like this!

[alex_read]

Where do you draw the line between Visual basic questions and VBA questions?

Any Excel questions whether the user is within Excel itself, or using the project library from within vb should be re-directed to this forum.

Obviously, if the user is using the object library, but the question isn't anything to do with excel - i.e. how to input text in a textbox in form2, when form1 is carrying out all the Excel work, this would be posted to the General Q&A forum...

[alex_read]

I thought you were trying to teach my granny to suck eggs then!

She's a pretty fast learner - picked it up far quicker than I did! it was easier to teach when she took her teeth out though!

It's okay until you get a rotton one....

[opus]

If i'd be a moderator, I'd have moved this post to the new forum.But since I suggested that Forum I'm trying to promote it also.
It was only yestrerday when I saw a post, where somebody tried to put about 3000 comboboxes into a EXCEL-sheet just to allow YESor NO as an input for those 3500 cells. And guess what, somebody gave him Code-lines instead of the EXCEL-menu commands. II figutred within a VBA Forum, the knowledge would be closer to the Appications.
But that is just my opinion!

[Mindcrime]

Top stuff Alex_read, thats working TopTasically!

-------

Originally posted by opus
If i'd be a moderator, I'd have moved this post to the new forum.But since I suggested that Forum I'm trying to promote it also.
It was only yestrerday when I saw a post, where somebody tried to put about 3000 comboboxes into a EXCEL-sheet just to allow YESor NO as an input for those 3500 cells. And guess what, somebody gave him Code-lines instead of the EXCEL-menu commands. II figutred within a VBA Forum, the knowledge would be closer to the Appications.
But that is just my opinion!

I can see your point, but my query is with the Visual Basic IDE and not Excel, Access or whatever.

I have found that a large amount of users using this site only deal with the General Forum. If my query was specific to VBA then I would have posted in their.

I'll try and use your suggested forum when I have a specific VBA question. I trust you will be on hand to help with my queries?

[opus]

Mindcrime,
don't feel offended, i just to put to word out that there is a new forum.
I know most people are using the General Section, that's OK, but don't feel it'S going to be a bit too much if you can't a post that has been answered 4 hours ago on the first page?.
And sure I'll try to answer your querries (up to my abilities, even if I have to build them just 'cause of the querry)

[macanannym]

This works great, simple too. VB.NET

Dim OBJExcelc As Excel.Application
OBJExcelc.Worksheets(1).Select()
Dim rowCount = OBJExcelc.ActiveSheet.UsedRange.Rows.Count
MsgBox(rowCount)

[nanoinfinity]

Though this thread got bumped up from what looks like years ago, it's probably archived by search engines so I figured I'd contribute.

If you use

ActiveSheet.UsedRange.Rows.Count

to count the last row of data in a sheet, you will get the wrong row value if there are blank rows anywhere before the first used row.
For example, put a value in a cell on row 10 of a blank worksheet and use ActiveSheet.UsedRange.Rows.Count . You will get "1" instead of "10".

If you use

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).row

as was suggested above, you will also get improper values under some circumstances. xlCellTypeLastCell seems to track NOT a cell that has a value in it, but cells that have been modified.

For example, put a value in cell A1 and run

msgbox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).row

from a macro. Then select any cell below that and go Edit->Clear->All and run the macro again. The last row value returned will now be whatever the cleared cell's row was.

I use a method provided here; I have not yet found a situation where it has failed to give the proper result. (This is also what user Shivaraj posted above):

ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

You can compare results yourself with this simple subroutine, ran on a new worksheet:

ActiveSheet.Cells(10, 1).Value = "test"
    MsgBox ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.Cells(1, 1).Value = "Blah"
    ActiveSheet.Cells(2, 1).Value = "hai"
    ActiveSheet.Cells(10, 1).Clear
    MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).row
    MsgBox ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

I can confirm that the above is true and works with Excel 03; I am unable to test other versions.

[dmaruca]

Another method you can use if you know a column which will always contain data for a row is

Worksheet.Range("B65536").End(xlUp)

Of course this LastRow.End(xlUp) type of thing would need to be updated for the new 2007 formats. The strength of this method is it is much faster than looping cells in a range which could take forever. It also uses no extra memory. The weakness is that other columns in the row could contain data thus providing a false positive.

If you have to loop every cell in a row to determine if it is blank or not, I'd recommend this method.

Dim val2 as Variant
Dim sht as Worksheet
Set sht = <somesheet>
val2 = sht.UsedRange.Value2

Now val2 will be a 2 dimension array or variants that you can loop through. The benefit of using this method is that arrays are a billion times faster to loop through than ranges.

[anhn]

1. As mentioned, ActiveSheet.UsedRange.Rows.Count returns number of rows in UsedRange,
it may not indicate the last used row on the worksheet if the first row of the UsedRange is not row 1.

The correct way should be like this:

LastUsedRow = ActiveSheet.UsedRange.Rows.Count - ActiveSheet.UsedRange.Row + 1

ActiveSheet.UsedRange.Row is the first row of the UsedRange, in case if that is 1 then
LastUsedRow = ActiveSheet.UsedRange.Rows.Count - 1 + 1

However, what does "UsedRange" mean?
Yes, it means the used range on a worksheet!!!
The last row and the last column of UsedRange may NOT contain any data if they was used before with data entered then cleared or cell formated.
So, be careful when using UsedRange to find last row or last column.
This may also happen the same way when using .SpecialCells(xlCellTypeLastCell)


2. The .End(xlUp) method can give only the last row that contains data on a specified column but more reliable.
If you want to use it without worry about Excel version (2003 or 2007) then do not hard code 65536 there. Use this:

With Activesheet '-- or any sheet
    LastRowOnColumn2 = .Cells(.Rows.Count, 2).End(xlUp).Row
End With

or without using [With] block:

LastRowOnColumn2 = Activesheet.Cells(Activesheet.Rows.Count, 2).End(xlUp).Row

.Rows.Count will return 65536 on Excel 2003 or 1048576 on Excel 2007.
Similarly, .Columns.Count will return 256 on Excel 2003 or 16384 on Excel 2007.

[ultima]

1. As mentioned, ActiveSheet.UsedRange.Rows.Count returns number of rows in UsedRange,
it may not indicate the last used row on the worksheet if the first row of the UsedRange is not row 1.

The correct way should be like this:

LastUsedRow = ActiveSheet.UsedRange.Rows.Count - ActiveSheet.UsedRange.Row + 1

ActiveSheet.UsedRange.Row is the first row of the UsedRange, in case if that is 1 then
LastUsedRow = ActiveSheet.UsedRange.Rows.Count - 1 + 1

However, what does "UsedRange" mean?
Yes, it means the used range on a worksheet!!!
The last row and the last column of UsedRange may NOT contain any data if they was used before with data entered then cleared or cell formated.
So, be careful when using UsedRange to find last row or last column.
This may also happen the same way when using .SpecialCells(xlCellTypeLastCell)


2. The .End(xlUp) method can give only the last row that contains data on a specified column but more reliable.
If you want to use it without worry about Excel version (2003 or 2007) then do not hard code 65536 there. Use this:

With Activesheet '-- or any sheet
    LastRowOnColumn2 = .Cells(.Rows.Count, 2).End(xlUp).Row
End With

or without using [With] block:

LastRowOnColumn2 = Activesheet.Cells(Activesheet.Rows.Count, 2).End(xlUp).Row

.Rows.Count will return 65536 on Excel 2003 or 1048576 on Excel 2007.
Similarly, .Columns.Count will return 256 on Excel 2003 or 16384 on Excel 2007.

IHow can the last code above -LastRowOnColumn2 = ActiveSheet.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).row --be modified to start with the first data row )10) and do a row count only to the first blank line? My worksheet is divided into two informal panels. To properly position the lower panel I need to get the last data line of the upper panel which may vary as rows are added or deleted in that panel.

[westconn1]

try

lastrowbeforeemptycell = cells(10, col).end(xldown).row

where col is a column number that will not be empty within the block