Macro help needed

[Blasting Cap]

I have a couple of worksheets that have customer number, name, address,
city, state & zip code in a couple of the worksheets.

The first list is about 3000 rows long, the second one is about 100 rows
long.

What I need to do is to write a macro to take the list in the second
sheet, find it if it exists in the first sheet, and delete the record in
the first sheet.

Can anyone help, or provide a source of some macro tutorials to do this
type of thing?

I would know how to do this inside SQL, but don't know how to do it in
Excel VBA.

Thanks,

BC

 

[Brian Withun]

Here's a go at it...

Private Sub CommandButton1_Click()

Dim DL As Worksheet
Dim ML As Worksheet

Dim DropRow As Integer
Dim MasterRow As Integer

Set DL = Sheets("DropList")
Set ML = Sheets("MasterList")

DropRow = 2 ' droppable names start in the 2nd row

While Not IsEmpty(DL.Cells(DropRow, 1))
MasterRow = 2 ' master list starts on 2nd row

While Not IsEmpty(ML.Cells(MasterRow, 1))

If ML.Cells(MasterRow, 1) = DL.Cells(DropRow, 1) And _
ML.Cells(MasterRow, 2) = DL.Cells(DropRow, 2) And _
ML.Cells(MasterRow, 3) = DL.Cells(DropRow, 3) And _
ML.Cells(MasterRow, 4) = DL.Cells(DropRow, 4) And _
ML.Cells(MasterRow, 5) = DL.Cells(DropRow, 5) And _
ML.Cells(MasterRow, 6) = DL.Cells(DropRow, 6) Then

ML.Rows(MasterRow).Delete
Else
MasterRow = MasterRow + 1
End If

Wend

DropRow = DropRow + 1

Wend

End Sub

You'll want to customize the two sheet names and make sure my row/
column offsets match where your data resides. This macro requires
that all 6 fields match up before it will delete the customer from the
master list. This may or may not be what you want. You can trim down
my large IF .. AND .. AND .. to suit your needs.

HTH

Brian Herbert Withun