On 21 Jul 2013 21:14:53 GMT, Malcolm Hind wrote:

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:=MyRan ge.Rows.Count - 2)
MsgBox "My Range is " & MyRange.Address

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