Find the last row.

[Mark@Work]

I think I've seen this question answered before,
but I couldn't find the relevent thread so I'll ask it again

How can I find the last "used" cell in a column?

The value might be text, or numeric.

I am importing data which may include 10 rows or 10000 rows,
I would like to have a cell (on sheet2) that tells me
(and any formulas I might use for analysing this data)
how many rows there are.

Mark.

[VBA Noob]

Hi,

Here's a collection of formulas to return the last value in a COLUMN
depending on the type/structure of data.
1. If you have no blank rows in between use
=OFFSET($A$1,COUNTA($A:$A)-1,0)

2. If you have blank rows in between try the following depending of the
type of values in your column:
2.a. If you have ONLY text values in column A try
=INDEX(A:A,MATCH(REPT("z",255),A:A))


2.b. If you have ONLY numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
or
=LOOKUP(9.99999999999999E307,A:A)


2.c. If you have BOTH types (text and values), but AT LEAST one text
and one numeric entry
=INDEX(A:A,MAX(MATCH(9.99999999999999E307,A:A),MATCH(REPT("z",255),A:A)
))


2.d. If you don't know the type of data use the following array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))


2.e If you don't want to count formula results like ="" as entry adapt
2.d. as follows:
=INDEX(A:A,MAX(IF(A:A<>"",0,ROW(A:A))))


Enjoy

VBA Noob

[stevekirk]

mark,

this will do it it is set to column A

Please Login or Register  to view this content.

STEVE

[Mark@Work]

One of those should do the trick,
Thanks.
Mark.

[Carim]

Hi,

for VBA coding ...

To Select Last Row used, using Column A as the reference Column

LastRow = Cells(Cells.Rows.Count,"A").End(xlUp).Row

To Find 1st Empty row, using Column A as the reference Column

Range("A65536").End(xlUp).Offset(1, 0).Select

or

Range("A1").End(xlDown).Offset(1,0).Select


HTH
Carim

[Bryan Hessey]

One of those should do the trick,
Thanks.
Mark.

nice collection, if all else fails set a function on

LastRow = ActiveSheet.Range("A65536").End(xlUp).Row

---