Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    54
    Thanks
    6
    Thanked 1 Time in 1 Post

    Excel 2003 to 2007 VBA range adjustment to determine last used row

    I have the following line of code to determine the last used row on the Data worksheet in Excel 2003:

    lEND = Worksheets("Data").Range("A65536").End(xlUp).Row

    Since Excel 2007 and 2010 have over a million rows, is it possible for the macro to determine the version of Excel the macro is being run on and to adjust this line of code accordingly?

    Thank you so much in advance.

    Larry

  2. #2
    Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    54
    Thanks
    6
    Thanked 1 Time in 1 Post
    I believe I solved it:

    If Val(Application.Version) < 9 Then Exit Sub
    If Val(Application.Version) < 12 Then
    lEND = Worksheets("Data").Range("A65536").End(xlUp).Row
    Else
    lEND = Worksheets("Data").Range("A1048576").End(xlUp).Row
    End If

    Thanks,
    Larry
    Last edited by lmo; 2012-02-23 at 15:35. Reason: debugged code

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Regardless of the Excel version, you can use:
    lEND = Worksheets("Data").Cells.SpecialCells(xlCellTypeLa stCell).Row
    or, for the used rows in column A:
    lEND = Worksheets("Data").Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row).End(x lUp).Row
    Last edited by macropod; 2012-02-24 at 02:55.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. The Following 2 Users Say Thank You to macropod For This Useful Post:

    lmo (2012-02-24)

  5. #4
    New Lounger
    Join Date
    Apr 2012
    Posts
    1
    Thanks
    0
    Thanked 1 Time in 1 Post

    Read Entire excel worksheet from vb.net

    you can use WorkSheet.UsedRange

    find the full source code here :

    http://vb.net-informations.com/excel..._worksheet.htm

    bikit.
    Last edited by macropod; 2012-04-17 at 05:30. Reason: Fixed broken link

  6. The Following User Says Thank You to bikithalee For This Useful Post:

    lmo (2012-04-17)

  7. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi bikit,

    Welcome to the Lounge. FWIW, WorkSheet.UsedRange is not a reliable way to find the last used row or column. For example, if some data are cleared, UsedRange still regards those cells as being used.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. The Following User Says Thank You to macropod For This Useful Post:

    lmo (2012-04-17)

  9. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, I would normally use
    Code:
    Worksheets("Data").Cells(rows.count, "A").end(xlup).Row


    xlcelltypelastcell suffers from the same issue as usedrange.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •