PDA

View Full Version : [SOLVED] How to fix last row in a worksheet



red bitroot
05-18-2005, 03:21 AM
I've got a worksheet with always 46 columns and a variable total of lines (218 for the moment).
I use following sentence 'LastRow = ActiveCell.SpecialCells(xlLastCell).Row' and it works fine
- it gives me LastRow = 218
- when I push CTRL-END, it select the cell "AT:46).

But :
1. if I delete 5 lines, LastRow = 218 (???)
2. if I use "save file..." without going out of my document, it gives now well
the correct information.

Question : how can I set the correct information (intern to Excel) without using the save option ?

Thanks a lot !

Red bitroot :hi:

Bob Phillips
05-18-2005, 03:26 AM
Question : how can I set the correct information (intern to Excel) without using the save option ?

Debra Dalgleish gives a programmatic solution at http://www.contextures.com/xlfaqApp.html#Unused

johnske
05-18-2005, 04:55 AM
Hi red bitroot, welcome to VBAX,

Try this


Sub FindLastRow()
Dim LastRow$
LastRow = Cells.Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox LastRow
End Sub

HTH,
John

red bitroot
05-18-2005, 05:35 AM
Thanks a lot to xld and Johnske.
The procedure mentionned by Johnske works very fine and is also very short.

Being a newbie in VBA for Excel, I'm not yet used to work with "find".

Red bitroot :hi:

johnske
05-18-2005, 05:38 AM
Not a prob. Red, just glad to see you got something working :thumb

Norie
05-18-2005, 07:16 AM
This is how I normally find the last row in a column.




LastRow = Range("A65536").End(xlUp).Row

Cyberdude
05-18-2005, 11:57 AM
Norie, me too.

johnske
05-18-2005, 02:56 PM
This is how I normally find the last row in a column.




LastRow = Range("A65536").End(xlUp).Row



Hi Norie,

That only finds the last entry in column A. That's fine if you assume there's always an entry in column A when there's an entry in any other column, but when that is not known for certain, you need to look at the other columns to find the last entry in that row. (there's several ways to do that - the code i gave above's one)

Regards,
John

red bitroot
05-19-2005, 11:54 PM
Again thanks a lot to everyone for your suggestions that I'll try.

I've also been looking for a solution (VBA Excel documentation, ...) and yesterday I tried the following instruction that works fine in my situation :


LastRow = ActiveSheet.UsedRange.Rows.Count

For example, when I've programmatically deleted lines :
1. if I press CTRL+END without this instruction, it selects the last cell of the previous full range ;
1. If I press CTRL+END after execution of this instruction, it selects the new really last cell. This instruction seems to adjust the line pointer correctly.

Kind regards

Red Bitroot :friends:

johnske
05-20-2005, 12:39 AM
Hi Red,

Yes, that's a fairly reliable solution, but for some unknown reason it did give me the wrong answer on one sheet that I tried, that's why I suggested the 'Find' solution (it was always correct).

But as long as it's giving you the correct answer for your own particular project, by all means use it. :thumb

For a little more discussion on this particular issue, you can also have a look here (http://www.mrexcel.com/td0058.html)

Regards,
John :beerchug:

MWE
05-20-2005, 11:37 AM
a slightly more general approach:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=417

xneurosis
01-24-2013, 03:06 AM
Thanks! This helped me out too.

Teeroy
01-24-2013, 09:29 PM
For general information (since the problem is solved) the UsedRange method works well if data begins from row 1, that is counting Rows in the UsedRange equals counting Rows on the worksheet. This isn't always the case.
A more general way of using UsedRange which works to get lastrow is


LastRow = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Rows.Count, 1).Row

or in a tidier form


With ActiveSheet.UsedRange
LastRow = .Cells(.Rows.Count, 1).Row
End With