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

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

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

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 -