Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Code
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),MAT CH(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(A 1:A10000)))) -- Regards Frank Kabel Frankfurt, Germany Todd Huttenstine wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Code
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),MA TCH(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,RO W (A1:A10000)))) -- Regards Frank Kabel Frankfurt, Germany Todd Huttenstine wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Code
"Todd Huttenstine" wrote in message
... Isnt there an XLdown or XLup method? 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 * -----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),MA TCH(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,RO W (A1:A10000)))) -- Regards Frank Kabel Frankfurt, Germany Todd Huttenstine wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Code
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 "Todd Huttenstine" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for counting the order cells are filled | Excel Discussion (Misc queries) | |||
Code for counting number of word strings? | Excel Discussion (Misc queries) | |||
counting function but not double counting duplicates | Excel Worksheet Functions | |||
Counting entries by color code | Excel Discussion (Misc queries) | |||
Counting names thru code | Excel Programming |