Hi
How can I clear all cells of a worksheet other than the active worksheet?

Thanks

do you mean other than the activeCell
v = activecell.Value
cells.ClearContents
activeCell.Value = v

--
Regards,
Tom Ogilvy

"bandy2000" wrote in message
...
Hi
How can I clear all cells of a worksheet other than the active worksheet?

Thanks

hi Tom,

no not active cell.
i have different worksheets and have to delete (clear) the complete contents
of
worksheets that are currently not active!
something like
Worksheets("TestSheet").Range.Cells.ClearContents
but this line doesn't work!


"Tom Ogilvy" wrote:

do you mean other than the activeCell
v = activecell.Value
cells.ClearContents
activeCell.Value = v

--
Regards,
Tom Ogilvy

"bandy2000" wrote in message
...
Hi
How can I clear all cells of a worksheet other than the active worksheet?

Thanks

Bandy

Worksheets("TestSheet").Cells.ClearContents works for me.

Note: "Range" removed.


Gord Dibben Excel MVP

On Wed, 16 Mar 2005 12:55:05 -0800, bandy2000
wrote:

hi Tom,

no not active cell.
i have different worksheets and have to delete (clear) the complete contents
of
worksheets that are currently not active!
something like
Worksheets("TestSheet").Range.Cells.ClearConten ts
but this line doesn't work!


"Tom Ogilvy" wrote:

do you mean other than the activeCell
v = activecell.Value
cells.ClearContents
activeCell.Value = v

--
Regards,
Tom Ogilvy

"bandy2000" wrote in message
...
Hi
How can I clear all cells of a worksheet other than the active worksheet?

Thanks

Thanks Gord,

i didn't get that right with the range....

Now it works

"Gord Dibben" wrote:

Bandy

Worksheets("TestSheet").Cells.ClearContents works for me.

Note: "Range" removed.


Gord Dibben Excel MVP

On Wed, 16 Mar 2005 12:55:05 -0800, bandy2000
wrote:

hi Tom,

no not active cell.
i have different worksheets and have to delete (clear) the complete contents
of
worksheets that are currently not active!
something like
Worksheets("TestSheet").Range.Cells.ClearConten ts
but this line doesn't work!


"Tom Ogilvy" wrote:

do you mean other than the activeCell
v = activecell.Value
cells.ClearContents
activeCell.Value = v

--
Regards,
Tom Ogilvy

"bandy2000" wrote in message
...
Hi
How can I clear all cells of a worksheet other than the active worksheet?

Thanks

Hello -

I have a similiar question. When the user deletes data from a cell in one
worksheet, I need to clear a range in another worksheet that corresponds to
the row where the cell was deleted.

Below is the code I'm using now in worksheet "Worksheet1". When A:"row
number" is cleared by the user in worksheet "Worksheet1", it will clear the
rest of that line. I need to add to this so when the cell is cleared, it
also clears the range F:"row number" from the "Worksheet2" worksheet.

----------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range

Set rngAllParentCells = Range("A6:A51")
Set rngDepCells = Intersect(Target, rngAllParentCells)

Application.ScreenUpdating = False

If Not rngDepCells Is Nothing Then
For Each rngCell In rngDepCells.Cells
'Move 1 cell to the right and clear contents
rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=2).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=3).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=4).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=5).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=6).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=7).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=8).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=9).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=10).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=11).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=12).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=13).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=14).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=15).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=16).ClearContents
rngCell.Offset(RowOffset:=0, ColumnOffset:=17).ClearContents
Next rngCell
End If
Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing

End Sub

-------------------------------

Since it's using a variable to identify the row and column, I'm not sure how
to identify the corresponding row and column in the other workbook.

Any help appreciated - THANKS!