Counting Code

T

Todd Huttenstine

Hey guys I have a list of values in range Column A:A.
There is a value in cell A7, then the next value in the
range is in cell A10, then A13. As you can see there is
value in every 3 cells. There maybe 700 values but they
span in a range from approximately A7:A2100.

I usually use the count or counta function in my loop
codes to specify the end of the range, but in this case
the count function will not work because there are many
blank cells in the range.

How do I look for the last value in column A:A and return
that cell number? For instance, the last value in Column
A:A is 1929. I need the code to return 1929.


Thank you
Todd Huttenstine
 
F

Frank Kabel

Hi
to get the last entry in a column use one of the following formulas:

0. if there're no blank rows in between you may use the following for
column A:
=OFFSET($A$1,COUNTA($A:$A)-1,0)

if you have blank rows in between tyr the following depending of the
type of values in your column:
1. If you have only text values in column A try
=INDEX(A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E300,A:A))

3. If you have both (text and values)
=INDEX(A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))
 
T

Todd Huttenstine

Isnt there an XLdown or XLup method?

-----Original Message-----
Hi
to get the last entry in a column use one of the following formulas:

0. if there're no blank rows in between you may use the following for
column A:
=OFFSET($A$1,COUNTA($A:$A)-1,0)

if you have blank rows in between tyr the following depending of the
type of values in your column:
1. If you have only text values in column A try
=INDEX(A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E300,A:A))

3. If you have both (text and values)
=INDEX(A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT (CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW (A1:A10000))))


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
R

Rob Bovey

Todd Huttenstine said:
Isnt there an XLdown or XLup method?
Click to expand...

Hi Todd

ActiveSheet.Range("A65536").End(xlUp).Value

Will give you the value in the last populated cell in column A (assuming you
haven't populated it all the way down to A65536).

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
R

Robert McCurdy

Try this Todd. Entered normally.

=INDEX(A:A,SUMPRODUCT(MAX(ROW(A7:A2100)*(A7:A2100<>""))))

In code it looks like this..

Sub LastValue()
Dim x As Variant
x = Evaluate("=Index(A:A,Sumproduct(Max(Row(A7:A2100)*(A7:A2100<>""""))))")
MsgBox x
End Sub


Regards Robert
 
Top