Results 1 to 7 of 7

Thread: How to get the row number of bottom usedrange?

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    47
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default How to get the row number of bottom usedrange?

    As new set of cells has to be copied at the bottom of existing used cells,
    but the VBA code below is failed (far lower than my used range),
    Code:
    a = ActiveCell.SpecialCells(xlLastCell).Row
    please advise the code.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,630
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to get the row number of bottom usedrange?

    If you want the Lastrow with data in column "A"
    Try this:
    Code:
    Sub Last_Row()
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    MsgBox Lastrow
    End Sub
    And be aware there are probable 20 other ways to do this also.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Jun 2016
    Posts
    47
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the row number of bottom usedrange?

    How about the lowest Lastrow with data in all used columns, not just Column A?

  4. #4
    Board Regular
    Join Date
    Nov 2016
    Posts
    246
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the row number of bottom usedrange?

    Hi,

    try

    Code:
    a =  Activesheet.cells.SpecialCells(xlLastCell).Row
    if there a formats below the data, this code could have problems.

    regards

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,322
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: How to get the row number of bottom usedrange?

    How about
    Code:
    Sub LstRw()
    
        Dim UsdRws As Long
    
        UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Jun 2016
    Posts
    47
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the row number of bottom usedrange?

    Thanks Fluff, it's work.
    Also thanks to My Aswer Is This, Fennek for suggestion.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,322
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: How to get the row number of bottom usedrange?

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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