Finding last constant in range

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

Walter Briscoe

I run Excel 2003.
I need to find the last constant in a range.
I have constructed a model of my data.
I start at A1 and the constants are in column 2.

My model's data is:
P Not0
O
N
M
L
K Not1
J
I
H
G Not2
F
E
D Not3
C
B Desired
A
Start

I have simple code to traverse the data.
Sub Macro1()
Dim List As Range
Dim Member As Range

' Excel renders lastcell - firstcell as firstcell - lastcell
Set List = Range(Cells(16, 2), Cells(1, 2))
' Grab constants in range
Set List = List.SpecialCells(xlCellTypeConstants)
For Each Member In List
Debug.Print Member.Address & " = " & Member.Value
Next Member
'
End Sub

That outputs
$B$1 = Not0
$B$6 = Not1
$B$10 = Not2
$B$13 = Not3
$B$15 = Desired

List consists of 5 members and I can easily get the desired datum
?list.Areas(list.Areas.Count)
Desired

If I add a datum to the end of my data
D Not3
C Not4
B Desired
A
Start

The range of 6 constants consists of 4 areas rather than 6.
I am looking for a 'simple' expression to find "Desired" - the last cell
in the range - in such a case.

I think my data always has the number of areas equal to the number of
constants, but would prefer not to rely on that assumption.

I went googling and this seems to work in both cases.
' Grab last constant
Set Member = List.Areas(List.Areas.Count)
Set Member = Member.Cells(Member.Cells.Count)
Debug.Print Member.Address & " = " & Member.Value

I now have a complete solution to my original problem.

What should I read to understand the structure of the range object?
 
Walter Briscoe said:
I run Excel 2003.
I need to find the last constant in a range.
I have constructed a model of my data.
I start at A1 and the constants are in column 2.

My model's data is:
P Not0
O
N
M
L
K Not1
J
I
H
G Not2
F
E
D Not3
C
B Desired
A
Start

I have simple code to traverse the data.
Sub Macro1()
Dim List As Range
Dim Member As Range

' Excel renders lastcell - firstcell as firstcell - lastcell
Set List = Range(Cells(16, 2), Cells(1, 2))
' Grab constants in range
Set List = List.SpecialCells(xlCellTypeConstants)
For Each Member In List
Debug.Print Member.Address & " = " & Member.Value
Next Member
'
End Sub

That outputs
$B$1 = Not0
$B$6 = Not1
$B$10 = Not2
$B$13 = Not3
$B$15 = Desired

List consists of 5 members and I can easily get the desired datum
?list.Areas(list.Areas.Count)
Desired

If I add a datum to the end of my data
D Not3
C Not4
B Desired
A
Start

The range of 6 constants consists of 4 areas rather than 6.
I am looking for a 'simple' expression to find "Desired" - the last cell
in the range - in such a case.

I think my data always has the number of areas equal to the number of
constants, but would prefer not to rely on that assumption.

I went googling and this seems to work in both cases.
' Grab last constant
Set Member = List.Areas(List.Areas.Count)
Set Member = Member.Cells(Member.Cells.Count)
Debug.Print Member.Address & " = " & Member.Value

I now have a complete solution to my original problem.

What should I read to understand the structure of the range object?
Click to expand...

There's not much to add. Where a Range comprises of multiple discontiguous
blocks of one or more cells, each block or "area" is itself a Range. An Area
may consist of a single cell (as in your original) or multiple cells in a
rectangle (as in the adjacent cells you added your last cell). Maybe this
will clarify -

Dim rngArea As Range, rngCell As Range
For Each rngArea In List.Areas
Debug.Print rngArea.Address
For Each rngCell In rngArea
Debug.Print , rngCell.Address
Next
Next

Regards,
Peter T
 
Back
Top