Clear corresponding cells in other worksheet

[Guest]

Hello -

Need a little help...

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
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!

 

[Guest]

Shelly,

Based on your description and current code, I think this code will
accomplish what you're trying to do. If any cell between A6 and A51 is
cleared, the entire row is cleared, and F:row number in "Worksheet2" is also
cleared.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row > 5 And Target.Row < 52 Then
If Target.Text = "" Then
Target.EntireRow.ClearContents
ThisWorkbook.Worksheets("Worksheet2").Range("F" &
Target.Row).ClearContents
End If
End If
End Sub

 

[Guest]

Thanks! This works well, except I need to delete the cells from F:row number
through N:row number. This is how I'm doing it... but I'm betting there is a
more efficient way. Is there?

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

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Column = 1 And Target.Row > 5 And Target.Row < 52 Then
If Target.Text = "" Then
Target.EntireRow.ClearContents
Worksheets("Interfaces").Range("F" & Target.Row).ClearContents
Worksheets("Interfaces").Range("G" & Target.Row).ClearContents
Worksheets("Interfaces").Range("H" & Target.Row).ClearContents
Worksheets("Interfaces").Range("I" & Target.Row).ClearContents
Worksheets("Interfaces").Range("J" & Target.Row).ClearContents
Worksheets("Interfaces").Range("K" & Target.Row).ClearContents
Worksheets("Interfaces").Range("L" & Target.Row).ClearContents
Worksheets("Interfaces").Range("M" & Target.Row).ClearContents
Worksheets("Interfaces").Range("N" & Target.Row).ClearContents
End If
End If

End Sub

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

 

[Guest]

Yes, try it this way:

Worksheets("Interfaces").Range("F" & Target.Row & ":N" &
Target.Row).ClearContents

 

[Guest]

Pefrect! Thank you!!