Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

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
or
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.