PDA

View Full Version : Solved: CODE TO CLEAR ROW CONTENTS BASED ON MATCH NAME



BENSON
06-17-2009, 06:02 AM
I have a workbook with 2 worksheets 1 & 2. Sheet one is a data base that starts with a persons name in colum "A" and other details in colums B,C,D etc. In worksheet 2 I have drop down list box linked to colum "A" in work sheet 1, I would like that when I select a name from the list box, the name and adjacent details relating to the person selected ,to be deleted from the data base on sheet 1

THKS

Bob Phillips
06-17-2009, 07:05 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2" '<== change to suit
Dim RowNum As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

RowNum = Application.Match(.Value, Worksheets("Sheet1").Columns(1), 0)
Worksheets("Sheet1").Rows(RowNum).Delete
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

BENSON
06-17-2009, 10:59 PM
Code works fine thanks, have changed it slightly to suit.Could code be altered further to not clear the entire row but specific cells. The cells that can be cleared are as follows:

A:E,H,K:L,Q:R,X:Y,AH:IV

THANKS

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
Dim RowNum As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

RowNum = Application.Match(.Value, Worksheets("TS GD").Columns(1), 0)
Worksheets("TS GD").Rows(RowNum).ClearContents
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Bob Phillips
06-18-2009, 12:56 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
Const RANGE_TO_CLEAR As String = _
"A<row>:E<row>,K<row>:L<row>,Q<row>:R<row>,X<row>:Y<row>,AH<row>:IV<row>"
Dim RowNum As Long
Dim rng As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

RowNum = Application.Match(.Value, .Columns(1), 0)
Worksheets("TS GD").Range(Replace(RANGE_TO_CLEAR, "<row>", RowNum)).ClearContents
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub