Use cell content as range address reference/count?

[tskogstrom]

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

 

[Trevor Shuttleworth]

Maybe:

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

Regards

Trevor

 

[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

 

[Trevor Shuttleworth]

MsgBox Range(Sheets("Sheet2").Range("A1").Value).Cells.Count

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

Regards

Trevor

 

[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

 

[Trevor Shuttleworth]

I just used your original data.

Sub test()
Debug.Print Sheets("Sheet2").Range("A1")
Debug.Print Range(Sheets("Sheet2").Range("A1").Value).Cells.Count
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


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