Results 1 to 3 of 3

Thread: Find Last Used Row In Column

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    Find Last Used Row In Column

    This finds last row in column "B"

    Code:
    With ActiveSheet
            LastRowCol = .Range("B" & .Rows.Count).End(xlUp).Row
    End With
    xl2007 - Windows 7
    xl hates the 255 number

  2. #2
    Junior Member Haseeb A's Avatar
    Join Date
    Apr 2011
    Posts
    21
    Rep Power
    0
    A formula option;

    If B:B contains only numbers,

    Code:
    =MATCH(1E+100,B:B)
    If B:B contains only texts,

    Code:
    =MATCH(REPT("Z",250),B:B)
    If B:B contains numbers & texts,

    Code:
    =MAX(LOOKUP(1E+100,CHOOSE({1,2},0,MATCH(REPT("Z",250),B:B))),LOOKUP(1E+100,CHOOSE({1,2},0,MATCH(1E+100,B:B))))

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rasm View Post
    This finds last row in column "B"

    Code:
    With ActiveSheet
            LastRowCol = .Range("B" & .Rows.Count).End(xlUp).Row
    End With
    I usually like to avoid concatenations wherever possible. VB offers an alternative to Range for specifying cells that avoids having to do concatenations...

    Code:
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    Note that there is no need to specify ActiveSheet as the object for the Range or Cells properties as that is the default when not specified.

    As for a formula method, this will also work...

    =SUMPRODUCT(MAX(ROW(B$1:B$1000)*(B$1:B$1000<>"")))

    where you would replace both 1000's with a number equal to or greater than the maximum row number you ever expect to have data in. While you can specify whole column references for this formula in XL2007 and XL2003, it is more efficient to limit the range to some realistic size that fits your actual needs. By the way, I used the SUMPRODUCT function to force the evaluation of the multi-cell range reference. If you don't mind using array-entered** formulas, the above can be shortened to this provided you array-enter** it....

    =MAX(ROW(B$1:B$65535)*(B$1:B$65535<>""))

    **Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Similar Threads

  1. Replies: 4
    Last Post: 04-05-2013, 12:08 PM
  2. Find the last row in a range
    By PcMax in forum Excel Help
    Replies: 7
    Last Post: 03-22-2013, 03:46 AM
  3. Find Last Used Row In A Column Using Long Variable
    By xander1981 in forum Excel Help
    Replies: 2
    Last Post: 01-27-2013, 08:53 PM
  4. Vba Code to find value and paste on certain row
    By jwitte in forum Excel Help
    Replies: 3
    Last Post: 11-28-2012, 08:52 PM
  5. Find The Last Entry Row In A Column That Contains Only Numbers
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-09-2011, 05:19 AM

Tags for this Thread

Posting Permissions

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