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:
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.