I have a beginners code question. My Sheet1 has a large table with 15
columns. If I delete an entry in any cell in Column A, I'd like the any
entries in columns C, D, G and I of the same row to also be deleted. What's
the best way to do this?

thanks
--
cinnie

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Text = "" Then
Range("C" & Target.Row & ":D" & Target.Row).ClearContents
Range("G" & Target.Row).ClearContents
Range("I" & Target.Row).ClearContents
End If
Application.EnableEvents = True
End Sub


--
Jacob


"cinnie" wrote:

I have a beginners code question. My Sheet1 has a large table with 15
columns. If I delete an entry in any cell in Column A, I'd like the any
entries in columns C, D, G and I of the same row to also be deleted. What's
the best way to do this?

thanks
--
cinnie

That **might** do more than the OP wants. If A1 was already blank and the
user clicked into it, then Column C, D, G and I would be erased without the
user doing anything. Just to offer the OP an alternative, the following code
will delete those other columns only when there was an entry in Column A and
it was deleted. To the OP, install this code the same way Jacob told you to
install his code...

'******************** START OF CODE ********************
Dim CellValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Value = "" Then
If CellValue < "" Then
Intersect(Target.EntireRow, Range("C:C,D:D,G:G,I:I")).ClearContents
End If
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then CellValue = Target.Value
End Sub
'******************** END OF CODE ********************

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Select the sheet tab which you want to work with. Right click the sheet
tab
and click on 'View Code'. This will launch VBE. Paste the below code to
the
right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Text = "" Then
Range("C" & Target.Row & ":D" & Target.Row).ClearContents
Range("G" & Target.Row).ClearContents
Range("I" & Target.Row).ClearContents
End If
Application.EnableEvents = True
End Sub


--
Jacob


"cinnie" wrote:

I have a beginners code question. My Sheet1 has a large table with 15
columns. If I delete an entry in any cell in Column A, I'd like the any
entries in columns C, D, G and I of the same row to also be deleted.
What's
the best way to do this?

thanks
--
cinnie

Thanks Rick. I thought about checking for previous data in ColA and using
Intersect; but finally thought to put it this way for a beginner..

--
Jacob


"Rick Rothstein" wrote:

That **might** do more than the OP wants. If A1 was already blank and the
user clicked into it, then Column C, D, G and I would be erased without the
user doing anything. Just to offer the OP an alternative, the following code
will delete those other columns only when there was an entry in Column A and
it was deleted. To the OP, install this code the same way Jacob told you to
install his code...

'******************** START OF CODE ********************
Dim CellValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Value = "" Then
If CellValue < "" Then
Intersect(Target.EntireRow, Range("C:C,D:D,G:G,I:I")).ClearContents
End If
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then CellValue = Target.Value
End Sub
'******************** END OF CODE ********************

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Select the sheet tab which you want to work with. Right click the sheet
tab
and click on 'View Code'. This will launch VBE. Paste the below code to
the
right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Text = "" Then
Range("C" & Target.Row & ":D" & Target.Row).ClearContents
Range("G" & Target.Row).ClearContents
Range("I" & Target.Row).ClearContents
End If
Application.EnableEvents = True
End Sub


--
Jacob


"cinnie" wrote:

I have a beginners code question. My Sheet1 has a large table with 15
columns. If I delete an entry in any cell in Column A, I'd like the any
entries in columns C, D, G and I of the same row to also be deleted.
What's
the best way to do this?

thanks
--
cinnie

.

Jacob - thanks for such a prompt reply. Your code fits the bill exactly. As
a learner, I'd like two ask 2 followups!

a) Why do you set EnableEvents to False while making the changes?

b) I actually need to use this code on 12 sheets, all with the same
structure (one for each month). How can I modify the code so it appears just
once in a general module instead of in each Sheet's code?

appreciating assistance
--
cinnie


"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Text = "" Then
Range("C" & Target.Row & ":D" & Target.Row).ClearContents
Range("G" & Target.Row).ClearContents
Range("I" & Target.Row).ClearContents
End If
Application.EnableEvents = True
End Sub


--
Jacob


"cinnie" wrote:

I have a beginners code question. My Sheet1 has a large table with 15
columns. If I delete an entry in any cell in Column A, I'd like the any
entries in columns C, D, G and I of the same row to also be deleted. What's
the best way to do this?

thanks
--
cinnie

Take a note of what Rick has pointed out...You can make use of
SelectionChange event to track the previous entry in ColA. and also minimize
the code by using Intersect...

1. This disables any other events which occurs parallel.

2. You can make use of the workbook Change event. From workbook press
Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search
for the workbook name and click on + to expand it. Within that you should see
the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 1 And Target.Text = "" Then
Application.EnableEvents = False
Range("C" & Target.Row & ":D" & Target.Row).ClearContents
Range("G" & Target.Row).ClearContents
Range("I" & Target.Row).ClearContents
Application.EnableEvents = True
End If
End Sub

--
Jacob


"cinnie" wrote:

Jacob - thanks for such a prompt reply. Your code fits the bill exactly. As
a learner, I'd like two ask 2 followups!

a) Why do you set EnableEvents to False while making the changes?

b) I actually need to use this code on 12 sheets, all with the same
structure (one for each month). How can I modify the code so it appears just
once in a general module instead of in each Sheet's code?

appreciating assistance
--
cinnie


"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Text = "" Then
Range("C" & Target.Row & ":D" & Target.Row).ClearContents
Range("G" & Target.Row).ClearContents
Range("I" & Target.Row).ClearContents
End If
Application.EnableEvents = True
End Sub


--
Jacob


"cinnie" wrote:

I have a beginners code question. My Sheet1 has a large table with 15
columns. If I delete an entry in any cell in Column A, I'd like the any
entries in columns C, D, G and I of the same row to also be deleted. What's
the best way to do this?

thanks
--
cinnie