Range Return (ECXCEL)

ok.. here is another question:

I have code that scans sheets for protected cells (Thanks DKenny ;) )

it fills out a new sheet with each cell range like this

Sheet1!A16
Sheet1!A17
etc...

what I would like to do is translate this into a range.. so I dont have 100 or 1000 individual cells listed..

I tried this:
Range("A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13").Select
then
Msgbox Selection.Address
but it returns the same as above $A$2,$A$3 etc..
I would like to get it to return A2:A13

thanks!

Hi,

You could use the Row and Column properties of the returned Cell object to figure out if they are in sequence, then construct a range from that.
I'm thinking something along the lines of putting the checker in a loop which identifies the row and column of the next Cell in the list - if neighbouring then stay in the loop and check for the next item, if not then end the current "range" and start another.

Does that make sense?

zaza

I had thought of that.. but wanted to see if there is an Easier solution ;)
Thanks!

ok, I got it :)

by setting the print area it figures the ranges correctly..
then I can just split it up how I want...
and reset the print area

VB Code:

'other code...
            WS.Activate
            WS.Range(tmpRange).Select
            tmpPA = WS.PageSetup.PrintArea
            WS.PageSetup.PrintArea = Selection.Address
            tmpRange = ActiveSheet.PageSetup.PrintArea
            WS.PageSetup.PrintArea = tmpPA
            'other code...