Excel VBA setting Ranges

[Malcolm Hind]

I would like to change the first Cell and the last Cell of a Range using
VBA

I have this code;

Dim MyRange As Range
Set MyRange = Selection

lets say the selection was ($A$1:$H$3), so I now have a MyRange of $A$1:$H
$3 and using;

MsgBox "My Range is " & MyRange.Address

I get a message that says that this is true.

Now I want to change the first Cell ($A$1) or the last Cell ($H$3) to
something different so I have tried these variations;

MyRange = Range(MyRange(1, 1).Address, H13) - Also tried using 'Set'
Set MyRange = Range("H3", "K8")

but using the message after either of these lines shows that the original
range limit is still applying.

Can anyone tell me how to set the first or last cell properties using VBA ?

Any help appreciated

Thanks

 

[Malcolm Hind]

MyRange(RowIndex:=2), MyRange(RowIndex:=MyRange.Rows.Count - 1

Ron, Thanks for that input - I have a couple of (sort of) follow up questions
but I will post them seperately.

 

[GS]

Further to Ron's`suggestion, perhaps...

Dim MyRange As Range
Set MyRange = Selection
With MyRange
.Cells(1) = 123: .Cells(,Cells.Count) = 456
End With 'MyRange

-OR- simply...

With Selection
.Cells(1) = 123: .Cells(,Cells.Count) = 456
End With 'Selection

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion