Hi,

I store the address of rows with a code like:
Sheet2.Range("A1").Value =
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address.

In cell A1 the content could look like:
$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V $100:$V$117,$V$124:$V
$140,$V$147:$V$163,$V$165:$V$233,$V$238:$V$289

I use to check if there has been any changes in the sheet since last
time visited by this code, buy a check in Worksheet_Activate event
like:
IF Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address <
Sheet2.Range("A1").value THEN ..., but that doesn't matter now. That
work ok. THE NEED NOW, is to know how many rows the address consist
of.

I thought I just could write like:
Debug.print Range(Sheet2.Range("A1").Value).Cells.Count

But I get error. What's wrong?

Kind regards
Tskogstrom

Maybe:

Sheet2.Range("A1").Value = Columns("V:V").SpecialCells(xlCellTypeFormulas,
1).Count

Regards

Trevor


"tskogstrom" wrote in message
oups.com...
Hi,

I store the address of rows with a code like:
Sheet2.Range("A1").Value =
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address.

In cell A1 the content could look like:
$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V $100:$V$117,$V$124:$V
$140,$V$147:$V$163,$V$165:$V$233,$V$238:$V$289

I use to check if there has been any changes in the sheet since last
time visited by this code, buy a check in Worksheet_Activate event
like:
IF Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address <
Sheet2.Range("A1").value THEN ..., but that doesn't matter now. That
work ok. THE NEED NOW, is to know how many rows the address consist
of.

I thought I just could write like:
Debug.print Range(Sheet2.Range("A1").Value).Cells.Count

But I get error. What's wrong?

Kind regards
Tskogstrom

Thank you for the suggestion

.... but I already have the address value stored. Wouldn't like to add
a further variable(=count) if there is any possible way to use the
stored address value instead.

Kind regards
Tskogstrom

MsgBox Range(Sheets("Sheet2").Range("A1").Value).Cells.Co unt

This returns 241 ... haven't checked if that's right

Regards

Trevor


"tskogstrom" wrote in message
oups.com...
Hi,

I store the address of rows with a code like:
Sheet2.Range("A1").Value =
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address.

In cell A1 the content could look like:
$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V $100:$V$117,$V$124:$V
$140,$V$147:$V$163,$V$165:$V$233,$V$238:$V$289

I use to check if there has been any changes in the sheet since last
time visited by this code, buy a check in Worksheet_Activate event
like:
IF Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address <
Sheet2.Range("A1").value THEN ..., but that doesn't matter now. That
work ok. THE NEED NOW, is to know how many rows the address consist
of.

I thought I just could write like:
Debug.print Range(Sheet2.Range("A1").Value).Cells.Count

But I get error. What's wrong?

Kind regards
Tskogstrom

Thank you Trevor,
now I understand why I get error with your code. When I let the code
store an range address, I get the areas separated with comma",".
Because the computer language define in the Excel GUI they should be
separated with semicomma ";"

I ran "," replaced by ";" in the cell with the address, and I got 241
as you did.

Hmm, now I dont know what to do. When I use the content in the cell
just to compare " IF Sheet2.Range("A1").Value<
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address
Then ..." it work OK. I guess This code wont work i I (by code)
replace the cell content from "," to ";"?

Any suggestions? Since this is a new question, I'll make a new tread
of it.

Kind re´gards,
Tskogstrom

I just used your original data.

Sub test()
Debug.Print Sheets("Sheet2").Range("A1")
Debug.Print Range(Sheets("Sheet2").Range("A1").Value).Cells.Co unt
End Sub

$V$10:$V$25,$V$33:$V$48,$V$54:$V$71,$V$77:$V$94,$V $100:$V$117,$V$124:$V$140,$V$147:$V$163,$V$165:$V$ 233,$V$238:$V$289
241

Try the code and data exactly as above.

Regards

Trevor


"tskogstrom" wrote in message
ups.com...
Thank you Trevor,
now I understand why I get error with your code. When I let the code
store an range address, I get the areas separated with comma",".
Because the computer language define in the Excel GUI they should be
separated with semicomma ";"

I ran "," replaced by ";" in the cell with the address, and I got 241
as you did.

Hmm, now I dont know what to do. When I use the content in the cell
just to compare " IF Sheet2.Range("A1").Value<
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address
Then ..." it work OK. I guess This code wont work i I (by code)
replace the cell content from "," to ";"?

Any suggestions? Since this is a new question, I'll make a new tread
of it.

Kind re´gards,
Tskogstrom