Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Cell in Range
Hi everybody,
I have a list of 6 numbers in a sheet named "Input" and in Cells "B3:G?". How can I, firstly find the last cell in the Range "B3:G?", and secondly create an array for example for each of the 6 numbers. So "B3:G3" will be one array, "B4:G4" will be another array and so on. I would like to be able to iterate through these then and perform some action. Thanks in Advance. All the Best. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Cell in Range
Two easy questtions
1) You need to get the last column using the equation below. columns.count is the last column in the worksheett which is 256 for excel 2003. XLTOLEFT says to start at the last column and find the first cell with data. Lastcolumn = cells(3,columns.count.end(xltoleft).column 2) Nextt you need to creatte a range for these cells set MyRange = Range(cells(3,"B"),cells(3,LastColumn)) 3) Once you set tthe frist item as a range you can put together ranges in a union set MyUnion = Union(MyRange1, Myrange2) "Paul Black" wrote: Hi everybody, I have a list of 6 numbers in a sheet named "Input" and in Cells "B3:G?". How can I, firstly find the last cell in the Range "B3:G?", and secondly create an array for example for each of the 6 numbers. So "B3:G3" will be one array, "B4:G4" will be another array and so on. I would like to be able to iterate through these then and perform some action. Thanks in Advance. All the Best. Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Cell in Range
Hi Paul,
Not sure I follow what you want with the arrays. "secondly create an array for example for each of the 6 numbers" That reads like you want six one element arrays, one for each number. I assume not, following is a guess at what you want. Sub test() Dim i as Long, nLastRow as Long Dim rng As Range Dim arr As Variant Set rng = Range("Input") With rng ' assumes a single area range nLastRow = .Rows.Count + .Rows(1).Row - 1 End With For i = 1 To rng.Areas(1).Rows.Count arr = rng.Rows(i).Value ' process arr Next End Sub Regards, Peter T "Paul Black" wrote in message ups.com... Hi everybody, I have a list of 6 numbers in a sheet named "Input" and in Cells "B3:G?". How can I, firstly find the last cell in the Range "B3:G?", and secondly create an array for example for each of the 6 numbers. So "B3:G3" will be one array, "B4:G4" will be another array and so on. I would like to be able to iterate through these then and perform some action. Thanks in Advance. All the Best. Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Cell in Range
Thanks Joel and Peter T.
All the Best. Paul On Aug 6, 11:33 am, "Peter T" <peter_t@discussions wrote: Hi Paul, Not sure I follow what you want with the arrays. "secondly create an array for example for each of the 6 numbers" That reads like you want six one element arrays, one for each number. I assume not, following is a guess at what you want. Sub test() Dim i as Long, nLastRow as Long Dim rng As Range Dim arr As Variant Set rng = Range("Input") With rng ' assumes a single area range nLastRow = .Rows.Count + .Rows(1).Row - 1 End With For i = 1 To rng.Areas(1).Rows.Count arr = rng.Rows(i).Value ' process arr Next End Sub Regards, Peter T "Paul Black" wrote in message ups.com... Hi everybody, I have a list of 6 numbers in a sheet named "Input" and in Cells "B3:G?". How can I, firstly find the last cell in the Range "B3:G?", and secondly create an array for example for each of the 6 numbers. So "B3:G3" will be one array, "B4:G4" will be another array and so on. I would like to be able to iterate through these then and perform some action. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
VBA REQUIRED TO TAKE VALUE FROM EACH CELL FROM A RANGE AND FIND THE VALUE FROM A DIFFERENT RANGE | Excel Programming | |||
Find first cell in range and expand range -VBA | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming |