Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear contents of cell
trying to have when an entry in row column (L) is made
clear contents of same row column (P) have following but doesn't clear as wanted thanks Dim lr As Long lr = Cells(Rows.Count, 200).End(xlUp).row If Not Intersect(Target, range("L200:L" & lr)) Is Nothing Then If IsNumeric(Target.Value) And Target.Value = 10 Then Sheets("data").range("P" & Target.row).ClearContents End If End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear contents of cell
Hi
You are trying to calculate 'lr' based on column 200, which I guess is not what you want. Dim lr As Long lr = Cells(Rows.Count, "L").End(xlUp).Row If Not Intersect(Target, Range("L200:L" & lr)) Is Nothing Then If IsNumeric(Target.Value) And Target.Value = 10 Then Application.EnableEvents = False Sheets("data").Range("P" & Target.Row).ClearContents Application.EnableEvents = True End If End If Regards, Per "Curt" skrev i meddelelsen ... trying to have when an entry in row column (L) is made clear contents of same row column (P) have following but doesn't clear as wanted thanks Dim lr As Long lr = Cells(Rows.Count, 200).End(xlUp).row If Not Intersect(Target, range("L200:L" & lr)) Is Nothing Then If IsNumeric(Target.Value) And Target.Value = 10 Then Sheets("data").range("P" & Target.row).ClearContents End If End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear contents of cell
Change your clear contents to .value=""
"Curt" wrote: trying to have when an entry in row column (L) is made clear contents of same row column (P) have following but doesn't clear as wanted thanks Dim lr As Long lr = Cells(Rows.Count, 200).End(xlUp).row If Not Intersect(Target, range("L200:L" & lr)) Is Nothing Then If IsNumeric(Target.Value) And Target.Value = 10 Then Sheets("data").range("P" & Target.row).ClearContents End If End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear contents of cell
You may also want to apply your sub to the last selected cell rather than the
active cell so that it will apply its action to the cell you have just filled. I use the following to retrieve the address of the previous cell. Dim LastSelected As String Private Sub Worksheet_SelectionChange(ByVal Target As Range) LastSelected = Target.Address End Sub Note that Dim lastselected as string appears above private sub worksheet selection change "Atishoo" wrote: Change your clear contents to .value="" "Curt" wrote: trying to have when an entry in row column (L) is made clear contents of same row column (P) have following but doesn't clear as wanted thanks Dim lr As Long lr = Cells(Rows.Count, 200).End(xlUp).row If Not Intersect(Target, range("L200:L" & lr)) Is Nothing Then If IsNumeric(Target.Value) And Target.Value = 10 Then Sheets("data").range("P" & Target.row).ClearContents End If End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear range contents when cell contents are changed by us | Excel Programming | |||
Clear Cell Contents | Excel Programming | |||
VBA Clear Cell Contents | Excel Programming | |||
vb to clear cell contents | New Users to Excel | |||
Clear cell contents with a button | Excel Worksheet Functions |