Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear corresponding cells in other worksheet
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear corresponding cells in other worksheet
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 "Shelly" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear corresponding cells in other worksheet
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 ------------------------- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear corresponding cells in other worksheet
Yes, try it this way:
Worksheets("Interfaces").Range("F" & Target.Row & ":N" & Target.Row).ClearContents "Shelly" wrote: 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 ------------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear corresponding cells in other worksheet
Pefrect! Thank you!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clear corresponding cells in other worksheet | Excel Programming | |||
clear contents cells of unprotected cells | Excel Programming | |||
Clear cells range if certain cells are all empty | Excel Programming | |||
Clear range of cells in different worksheet | Excel Programming | |||
Clear the content of a worksheet using VBA | Excel Programming |