Tags: a65536, cell, column, excel, identify, microsoft, million, msdn, occupied, range, rows, silly, software, vba, xlup

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

On Microsoft » Microsoft Excel

1,681 words with 4 Comments; publish: Fri, 23 May 2008 01:31:00 GMT; (30662.50, « »)

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

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

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.questionfor.info.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