Find Last Cell in Range

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

Paul Black

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
 
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)
 
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
 
Thanks Joel and Peter T.

All the Best.
Paul

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








- Show quoted text -
Click to expand...
 
Back
Top