Reference Row Address

[scain2004]

If I have a range named Datalist and I want to get the address for ever
row that contains values in the third column, how can I get thi
address and put it into an array for use with a scrollbar.

Any suggestions

 

[Norman Jones]

Hi,

For xl2k onwards, if the values are constants, you could try:

Sub Test()

Dim Rng As Range
Dim v As Variant

Set Rng = Range("Datalist").Columns(3)
.Cells.SpecialCells (xlCellTypeConstants)

msgbox Rng.Address
v = Split(Rng.Address, ",")

End Sub

If you are using an earlier version, do a google search for a Split97
function written by Tom Ogilvy and use instes of the Split function
employed above.

 

[Norman Jones]

If your values are formulae, then replace xlCellTypeConstants with
xlCellTypeFormulas. If youor data includes both , just build a union range
for both.