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