delete contents of cell
 

I have two questions, firstly I want to delete a cells value (it has a
numerical value in it) by typing the word "dead" into another cell.

Secondly, I have large spreadsheet with deals on, can I somehow run a macro
to remove all those deals with the "Dead" in the row to another spreadsheet?

Thanks

delete contents of cell
 

This is only the first part. Say we have data in column B. If we enter
"dead" in column A we want the adjacent cell in column B to be cleared. Put
this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set A = Range("A:A")
If Intersect(t, A) Is Nothing Then Exit Sub
If UCase(t.Value) = "DEAD" Then
Application.EnableEvents = False
t.Offset(0, 1).Clear
Application.EnableEvents = True
End If
End Sub

Say we have data in column B and already have some "dead"s in column A and
we want to do some cleanup. Run this macro:

Sub oldData()
n = Cells(Rows.Count, 1).End(xlUp).Row
Application.EnableEvents = False
For i = 1 To n
If UCase(Cells(i, 1).Value) = "DEAD" Then
Cells(i, 2).Clear
End If
Next
End Sub

--
Gary''s Student - gsnu200828


"RDC" wrote:

I have two questions, firstly I want to delete a cells value (it has a
numerical value in it) by typing the word "dead" into another cell.

Secondly, I have large spreadsheet with deals on, can I somehow run a macro
to remove all those deals with the "Dead" in the row to another spreadsheet?

Thanks