PDA

View Full Version : Solved: Delete rows from another sheet (Matching)



dvenn
08-29-2006, 12:37 PM
Looking for a method to delete rows from another sheet based of values the active sheet.

On 1 sheet (activesheet) I have Column A

1002
1992
43982
104432
3847

on another Sheet I have Column A

1002
1002
1002
104432
1002
104432
234


What I want is to look at the activesheet values, if the value is present in the other sheet delete that row (but delete every occurence).

This example the only row that should be left is the 234 Row

Sugestions?

Zack Barresse
08-29-2006, 12:50 PM
Hey there,

This may work for you ...

Sub RemoveFromOtherSheet()
Dim c As Range
Application.DisplayAlerts = False
For Each c In Selection
With Sheets("LookIn") 'range to delete from
.AutoFilterMode = False
.Range("A:A").AutoFilter field:=1, Criteria1:=c.Value
.Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeVisible).Delete
.AutoFilterMode = False
End With
Next c
Application.DisplayAlerts = True
End Sub

Select the range of cells in your first sheet (the activesheet). Change the second sheet name to whatever your sheet name is.

HTH

dvenn
08-29-2006, 12:56 PM
AS usual Zack..

A quick yet extremely effective answer!!

Zack Barresse
08-29-2006, 12:57 PM
Glad it works for you Daniel. :yes