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

 

[Ron Rosenfeld]

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

Here are some illustrations. This Macro should be in a regular module

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===============================
Option Explicit
Sub RangeSetting()
Dim MyRange As Range
Set MyRange = Selection
MsgBox "My Range is " & MyRange.Address

'Remove 1st and last cell
Set MyRange = Range(MyRange(RowIndex:=2), MyRange(RowIndex:=MyRange.Rows.Count - 1))
MsgBox "My Range is " & MyRange.Address

Set MyRange = Range("H3", "K8")
MsgBox "My Range is " & MyRange.Address

'now change it to H4:K7
Set MyRange = MyRange.Offset(rowoffset:=1).Resize(rowsize:=MyRange.Rows.Count - 2)
MsgBox "My Range is " & MyRange.Address

End Sub
=========================
===============================

 

[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