PDA

View Full Version : [SOLVED] match delete vba



Sandler
05-25-2016, 06:38 AM
If sheet 2 shows a specific number in column A, I want it to delete the row that in Sheet1 that has a matching number in column 3.

Please, advise the quickest way to look through multiple numbers in sheet2 and delete all matches in Sheet1.

SamT
05-25-2016, 08:12 AM
Loop thru the used cells in column A

Use iterative "Find" on Column C, Be sure to Set "From" = C1 and "Direction" to xlPrevious

mdmackillop
05-25-2016, 11:27 AM
Some data type assumptions made

Sub Macro1()


Dim Crit()
Dim Data As Range, cel As Range
Dim r As Range
Dim i As Long

Set r = Sheets(2).Columns("A:A").SpecialCells(xlCellTypeConstants)
ReDim Crit(r.Cells.Count - 1)
For Each cel In r
Crit(i) = Str(cel)
i = i + 1
Next
Set Data = Sheets(1).Columns("C:C").SpecialCells(xlCellTypeConstants)
Data.AutoFilter Field:=1, Criteria1:=Crit, Operator:=xlFilterValues
Data.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub

Sandler
05-25-2016, 11:47 AM
Many Thanks!