Delete rows if specific multiple values exist using Macros.

[Honey]

Dear Chandoo,


I really need your help on priority basis about this macro puzzle. I want to delete rows if specific multiple values exist in a particular column and number of values are variable one day i have 250, another day 300 and this can be lesser than 250 as well. For e.g let's say Header name is (source codes and it contains 111 222 333 444 555 666 so on) now i want to delete rows which contain source code 222 and 666 out of all other source codes irrespective of the number of rows (means source code 222 has 700 rows and 666 1500) with help of macro. Looking forward to see your response soon. Apart from this i would also like to say thanks to you for sharing invaluable learning experience with others.

 

[Clarity]

Hi,


The Macro below is a basic delete and loop macro. Simply enter the value you want to delete and the number of iterations in the VBA and then in Excel select the first cell to evaluate. The macro will then run down the rows deleting the entire row where it find the entry you have selected. (the macro will current


Sub Loop_Delete_Macro()


Dim Counter As Integer

Dim Todelete As String

Dim NoIterations As Integer


'DEFINE VALUE TO DELETE

Todelete = 555


'DEFINE NUMBER OF ITERATIONS (IE HOW MANY ROWS YOU HAVE IN TOTAL)

NoIterations = 50


Counter = 0


Do While Counter < NoIterations


If ActiveCell.Value = Todelete Then


Selection.EntireRow.Delete


Else: ActiveCell.Offset(1, 0).Activate


End If

Counter = Counter + 1


Loop


End Sub


Obviously you can tweak the above code to suit your exact requirements.

 

[kchiba]

Here is a Macro called DeleteRow


On a sheet named "Params" create a two column list with the criteria you want to delete in the left column and the column that the data is in in the right column


Criteria Column

aaa A

bbb B


This will delete all rows where there is aaa in column A and then all rows where there is bbb in column B


You can make this list as long as you like, but do not have any other entries around the cells


Name the cell with with Criteria "DelCriteria"


The Data is on the sheet called "Data"


Sub DeleteRows()

Dim rCriteria As Range

Dim xRows As Integer


Set rCriteria = ThisWorkbook.Sheets("params").Range("DelCriteria")

Set rCriteria = rCriteria.CurrentRegion

xRows = rCriteria.Rows.Count


For i = 1 To xRows - 1

tCol = Range("DelCriteria").Offset(i, 1).Value

tCrit = Range("DelCriteria").Offset(i, 0).Value


Sheets("Data").Activate

lRow = Sheets("Data").Range(tCol & 65536).End(xlUp).Row

For d = lRow To 1 Step -1

Sheets("Data").Range(tCol & d).Select

If Selection = tCrit Then

Selection.EntireRow.Delete

End If

Next d


Next i


End Sub