Past Tip of the Day
This is my first tip of the day, and with it, i'll try to face one common question using VBA in Excel. That is "how can I find the last used row in a particular sheet ?". There are several methods to accomplish this, some more exact than others, but they can all serve your needs.
One common method
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.
Another method to find the last used row in a particular column is:
LastRowColA = Range("A65536").End(xlUp).Row
but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.
A couple extra methods are more reliable.
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
LastRow = ActiveSheet.UsedRange.Rows.Count
This methods can be used on any sheet, not just the active sheet.
By Juan Pablo Gonzalez on 15-Jan-2002
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Excel is a registered trademark
of the Microsoft® Corporation.
All contents Copyright
1998-2008 by MrExcel Consulting.