I use a message box in my macro that delivers a cell address. This aspect of
the macro work well. The following line I think is the problem:

ActiveCell.Offset(1, 0).Resize(1, 17).ClearContents

It clears the row below the one required. Is Offset the correct way to go?

AlexW

The correct formula depends on what you want to do and in your example below
the 'offset' statement makes Excel select the next cell down from the active
cell and you then resize that selection to 17 cells. The 1 in the resize
statement isn't strictly necessary. If you simply want to resize from the
active cell use

ActiveCell.Resize(, 17).ClearContents

Mike

"Alex.W" wrote:

> I use a message box in my macro that delivers a cell address. This aspect of
> the macro work well. The following line I think is the problem:
>
> ActiveCell.Offset(1, 0).Resize(1, 17).ClearContents
>
> It clears the row below the one required. Is Offset the correct way to go?
>
> AlexW

You want to preserve the activecell contents and delete 17 cells to the right?

ActiveCell.Offset(0, 1).Resize(1, 17).ClearContents


Gord Dibben MS Excel MVP


On Tue, 8 Apr 2008 21:01:00 -0700, Alex.W <(E-Mail Removed)>
wrote:

>I use a message box in my macro that delivers a cell address. This aspect of
>the macro work well. The following line I think is the problem:
>
>ActiveCell.Offset(1, 0).Resize(1, 17).ClearContents
>
>It clears the row below the one required. Is Offset the correct way to go?
>
>AlexW

Thanks Mike, it works a treat.
AlexW

"Mike H" wrote:

> The correct formula depends on what you want to do and in your example below
> the 'offset' statement makes Excel select the next cell down from the active
> cell and you then resize that selection to 17 cells. The 1 in the resize
> statement isn't strictly necessary. If you simply want to resize from the
> active cell use
>
> ActiveCell.Resize(, 17).ClearContents
>
> Mike
>
> "Alex.W" wrote:
>
> > I use a message box in my macro that delivers a cell address. This aspect of
> > the macro work well. The following line I think is the problem:
> >
> > ActiveCell.Offset(1, 0).Resize(1, 17).ClearContents
> >
> > It clears the row below the one required. Is Offset the correct way to go?
> >
> > AlexW