How to get the row number of bottom usedrange?

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
    41
    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
    10,875
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 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
    41
    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
    219
    Post Thanks / Like
    Mentioned
    2 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
    5,204
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 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 guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Jun 2016
    Posts
    41
    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
    5,204
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

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

    ††
    Glad we could help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

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
  •  

 

DMCA.com