Find Last Used Row In Column

**Rasm** · 04-14-2011, 03:34 AM

This finds last row in column "B"

Code:

With ActiveSheet
        LastRowCol = .Range("B" & .Rows.Count).End(xlUp).Row
End With

**Haseeb A** · 04-14-2011, 06:33 AM

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))))

**Rick Rothstein** · 06-04-2012, 07:40 AM

Originally Posted by Rasm

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.