Your One Stop for Excel Tips & Solutions


 

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

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 - MrExcel.com   Google+ MrExcel.com   Facebook - Bill Jelen   Pinterest - Bill Jelen

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

 

 

 

 

 

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

 

Spread the Word Excel
submit to reddit
Search MrExcel.com
150K Pages of Excel


The Daily News  - with Bill 'MrExcel' Jelen
 
"Power Excel 2013" with Bill Jelen a.k.a. MrExcel!
"Easy-XL" from MrExcel!
 
 
           

MrExcel.com debuted on November 21, 1998.

MrExcel.com 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. This site contains affiliate links. Any affiliate commissions that we earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com running. You can earn a commission for sales leads that you send to us by joining our affiliate program. View my Privacy Policy.

Excel ® is a registered trademark of the Microsoft Corporation.
MrExcel ®
is a registered trademark of Tickling Keys, Inc.

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