Your One Stop for Excel Tips & Solutions


MrExcel - Photos of MrExcel
About MrExcel
Consulting Services
Learn Excel Resources
Excel - Visual Learning
Challenge of the Month
MrExcel Seminars
Message Board
MrExcel Store
Learn Excel Blog
Having an Excel Emergency?
Microsoft Store

Read The Latest...

Learn Excel Blog   MrExcel on YouTube   Excel Daily News   Amazon MrExcel Books

Let's Connect

Twitter @MrExcel   Facebook MrExcel   LinkedIn Bill Jelen   Google+ Bill Jelen

LinkedIn -   Google+   Facebook - Bill Jelen   Pinterest - Bill Jelen

GoodReads - Bill Jelen   About.Me Bill Jelen   RSS - MrExcel Forum Hot Topics   RSS - Learn Excel Blog


Click for the BBB Business Review of this Computers - Training in Merritt Island FL





QQube for Quickbooks Information Page

Excel: Find the last row of data in a range

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


Spread the Word Excel
submit to reddit
150K Pages of Excel

The Daily News  - with Bill 'MrExcel' Jelen
"Power Excel 2013" with Bill Jelen a.k.a. MrExcel!
    debuted on November 21, 1998. provides examples of Formulas, Functions and 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 Formulas, Functions and 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.
MrExcel ®
TM is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2015 by MrExcel Consulting | All rights reserved