I have Excel 97 and have a spreadsheet that contains individuals
results set out in each row. I have a macro which wipes all the info
on the sheet but I need a button located at the start of the
individuals row which allows me to delete just that row. Some of the
cells in each row are protected so, to work around this, I have set up
offsets to select the required cells for me such as -

ActiveCell.Offset(0, 11).Select
Selection.ClearContents
Selection.ClearComments

The problem I am having is that the active cell is the last cell I
entered data into... I cannot find a way to make the active cell the
cell underneath or to the right of the command button I press, hence
data is wiped in the wrong place.

Can someone please shed some light on this problem for me?

Hi
maybe something like
with activecell.offset(1,1)
.clearcontents
-clearcomments
end with

No need for selecting the cell


--
Regards
Frank Kabel
Frankfurt, Germany


Marvin wrote:
I have Excel 97 and have a spreadsheet that contains individuals
results set out in each row. I have a macro which wipes all the info
on the sheet but I need a button located at the start of the
individuals row which allows me to delete just that row. Some of the
cells in each row are protected so, to work around this, I have set
up
offsets to select the required cells for me such as -

ActiveCell.Offset(0, 11).Select
Selection.ClearContents
Selection.ClearComments

The problem I am having is that the active cell is the last cell I
entered data into... I cannot find a way to make the active cell the
cell underneath or to the right of the command button I press, hence
data is wiped in the wrong place.

Can someone please shed some light on this problem for me?

I don't think this will work either. I can't think of a
way of doing what you want to do without selecting at
least a cell in the row that you want, Unless each button
has its own code, in which case you could make the button
select the cell (or a cell in the row) that you want to
delete.

Try something like
Cells({Row},{Column}).select early in your code
where {Row} and {Column} are the row and column numbers of
the cell that you want to select, before applying your
clear contents routine.

Trev

-----Original Message-----
Hi
maybe something like
with activecell.offset(1,1)
.clearcontents
-clearcomments
end with

No need for selecting the cell


--
Regards
Frank Kabel
Frankfurt, Germany


Marvin wrote:
I have Excel 97 and have a spreadsheet that contains
individuals
results set out in each row. I have a macro which wipes
all the info
on the sheet but I need a button located at the start
of the
individuals row which allows me to delete just that
row. Some of the
cells in each row are protected so, to work around
this, I have set
up
offsets to select the required cells for me such as -

ActiveCell.Offset(0, 11).Select
Selection.ClearContents
Selection.ClearComments

The problem I am having is that the active cell is the
last cell I
entered data into... I cannot find a way to make the
active cell the
cell underneath or to the right of the command button I
press, hence
data is wiped in the wrong place.

Can someone please shed some light on this problem for
me?

.

I think I'd use one button in row 1. Then windows|Freeze panes so that row 1 is
always visible.

then you could use the activecell to know which cells to clear.

But...
if you really have a button from the Forms toolbar on each row, then you could
do this:

Option Explicit
Sub testme()

Dim myBTN As Button

Set myBTN = ActiveSheet.Buttons(Application.Caller)

With ActiveSheet.Cells(myBTN.TopLeftCell.Row, 5).Resize(1, 10)
.ClearComments
.ClearContents
End With

End Sub


You can assign the same macro to all the buttons--if they're from the Forms
toolbar.

And I cleared columns E through N. (column 5 resized for 10 columns)

Marvin wrote:

I have Excel 97 and have a spreadsheet that contains individuals
results set out in each row. I have a macro which wipes all the info
on the sheet but I need a button located at the start of the
individuals row which allows me to delete just that row. Some of the
cells in each row are protected so, to work around this, I have set up
offsets to select the required cells for me such as -

ActiveCell.Offset(0, 11).Select
Selection.ClearContents
Selection.ClearComments

The problem I am having is that the active cell is the last cell I
entered data into... I cannot find a way to make the active cell the
cell underneath or to the right of the command button I press, hence
data is wiped in the wrong place.

Can someone please shed some light on this problem for me?

--

Dave Peterson