PDA

View Full Version : Solved: Code to find match & remove data from 5 sheets



Barryj
01-14-2009, 09:08 AM
I have a userfom populated with names from sheet 1, what I want to happen is when a name is selected on the userform and the delete button is clicked that it will search sheets 2,3,4,5,6 for a match to the name from the userform and delete the data from each sheet with a match.

The data that needs to be deleted is name from column A and all data in that row up to column W.

I have attached a workbook with the userform and the other sheets.

Thanks for any assistance.

nst1107
01-14-2009, 09:25 AM
This does not account for multiple instances of a name or no instance of a name. It assumes that if there is an entry on Sheet1, there will be a corresponding entry on the rest of the sheets.Private Sub CommandButton1_Click()
Dim c As Range
Dim ws As Worksheet
If ComboBox1 = vbNullString Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
If ws.Index = Sheet1.Index Then GoTo escape
With ws.Columns("A")
Set c = .Find(ComboBox1)
If Not c Is Nothing Then ws.Rows(c.Row).Delete
End With
escape:
Next
End Sub

xld
01-14-2009, 09:27 AM
Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim cell As Range

For Each sh In Worksheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6"))

Set cell = Nothing
Set cell = sh.Columns(1).Find(Me.ComboBox1.Value)
If Not cell Is Nothing Then

cell.EntireRow.Delete
End If
Next sh
End Sub

Barryj
01-14-2009, 09:41 AM
Thank you both nst1007 and xld for your code, I think xld's code suits my situation best, thanks again to you both, I will mark this as solved.