In Excel VBA, how can I identify last occupied cell in a column?

Explore Related Content On "Microsoft Excel"

• Hot Articles & Questions on "Microsoft Excel"
• In Excel trying to reference text that already is in other cells?
• In excel the tabs at the bottom of the page are gone.
• In excel the sum is not calculating
• In excel the gridlines wont print on spreadsheets?
• In Excel the colums which used to be A,B,C D etc are now 1,2,3
• In Excel Sheet how I can give Number bullets in the series of cell
• In excel reverse a minus figure to a plus -1.81 to 1.81
• In excel print header on firt page only
• In excel pointers should be availible
• in excel my down arrow scrolls the page down instead moving to th.

• Open Articles & Questions on "Microsoft Excel"
• In Excel vba how do you Launch code a standalone Module from a Form or other Module ?
• In Excel vba how do you Launch code a standalone Module from a Form or other Module ?
• In Excel VBA Editor, how do I hide my code from other users?
• In Excel using URLs, how to display pictures stored on a server?
• in excel using page numbers without using header & footer
• In Excel tracked changes, how do I use a different text colour?
• in excel totaling weekly hours military time
• In Excel Tools Menu Option Dialog box don't open
• In Excel the total is off by a penny.
• In excel the formula bar behaves differently on different machines

All Comments

Leave a comment...

• 4 Comments
• • 
  • Excel isn't looping up the worksheet I wouldn't think. My guess is it knows

    where things are. I wouldn't be concerned that it would be much slower.

    If Val(Application.Version) <12 Then

    'not in Excel 2007

    LastUsedRow = cells(Rows.Count,1).End(xlup).row

    Else

    'in Excel 2007 (or later)

    LastUsedRow = cells(Rows.CountLarge,1).End(xlup).row

    End If

    I would guess - although I don't have xl2007 readily available to test it.

    Note the use of CountLarge.

    --

    regards,

    Tom Ogilvy

    "Rick House" wrote:

    > I used to use Range("A65536").End(xlUp)).Count

    > but it seems silly with over a million rows (Excel 2007).

    #1; Fri, 23 May 2008 01:32:00 GMT
• • 
  • Hi Rick,

    With ActiveSheet

    MsgBox .Cells(Rows.Count, 1).End(xlUp).Row

    End With

    --

    Greetings from Bavaria, Germany

    Helmut Weber, MVP WordVBA

    Win XP, Office 2003

    "red.sys" & Chr$(64) & "t-online.de"

    #2; Fri, 23 May 2008 01:33:00 GMT
• • 
  • Hi Tom,

    >CountLarge

    I'll be on 2007 soon.

    Thx.

    --

    Greetings from Bavaria, Germany

    Helmut Weber, MVP WordVBA

    Win XP, Office 2003

    "red.sys" & Chr$(64) & "t-online.de"

    #3; Fri, 23 May 2008 01:34:00 GMT
• • 
  • Rick House <Rick H....excel.itags.org.discussions.microsoft.com> wrote...

    >I used to use Range("A65536").End(xlUp)).Count

    >but it seems silly with over a million rows (Excel 2007).

    Well the last used cell in a column would always be within the

    worksheet's UsedRange. If Excel's object model automatically moves up

    to the UsedRange before actually checking cell contents, no big deal

    in XL2007.

    #4; Fri, 23 May 2008 01:35:00 GMT