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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.