Delete Macro using Offset

[Alex.W]

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

 

[Mike H]

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

 

[Gord Dibben]

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

 

[Alex.W]

Thanks Mike, it works a treat.
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