Need help please for deleting rows that does not meet several criteria
 

Hi All,

Can someone please help me if a macro function in excel can help delet
rows that does not meet several conditions.

Example:

For a range the macro will delete the row if it does not contain th
following characters

*ADT7461*
*REF19*
*AD623*
*AD8200*
*ADXL330*
*OP291*
*AD1555*
*AD5561*
*COLD*

the number of rows is up to 20,000 and if possible i can just assig
the conditions in a range in another worksheet so that if an additiona
condition is placed it will automatically be included.

I need the help of your excel guru gods please....

this is already beyond my excel know-ho

--
Ali
-----------------------------------------------------------------------
Alib's Profile: http://www.officehelp.in/member.php?userid=661
View this thread: http://www.officehelp.in/showthread.php?t=136770

Posted from - http://www.officehelp.i

Need help please for deleting rows that does not meet several crit
 

You don't provide too much information so with the assumption the values you
are looking for are in column A try:-

Sub Deleterows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If rng(i).Value = "ADT7461" _
Or rng(i).Value = "REF19" _
Or rng(i).Value = "AD623" _
Or rng(i).Value = "AD8200" _
Or rng(i).Value = "ADXL330" _
Or rng(i).Value = "OP291" _
Or rng(i).Value = "AD1555" _
Or rng(i).Value = "AD5561" _
Or rng(i).Value = "COLD" Then
rng(i).EntireRow.Delete
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Mike

"Alib" wrote:


Hi All,

Can someone please help me if a macro function in excel can help delete
rows that does not meet several conditions.

Example:

For a range the macro will delete the row if it does not contain the
following characters

*ADT7461*
*REF19*
*AD623*
*AD8200*
*ADXL330*
*OP291*
*AD1555*
*AD5561*
*COLD*

the number of rows is up to 20,000 and if possible i can just assign
the conditions in a range in another worksheet so that if an additional
condition is placed it will automatically be included.

I need the help of your excel guru gods please....

this is already beyond my excel know-how


--
Alib
------------------------------------------------------------------------
Alib's Profile: http://www.officehelp.in/member.php?userid=6619
View this thread: http://www.officehelp.in/showthread.php?t=1367706

Posted from - http://www.officehelp.in

Need help please for deleting rows that does not meet several criteria
 

Hi Mike,

This is definitely another way of doing the program in deleting th
rows and I have learned another way of doing it instead of my usual D
Loop Until process.

However, if I put the conditions in another tab in excel. Lets say fo
example, all the conditions is placed in column A of Sheet2. The macr
will look up to these conditions until the last condition, (in thi
case anything COLD is the last condition).
*ADT7461*
*REF19*
*AD623*
*AD8200*
*ADXL330*
*OP291*
*AD1555*
*AD5561*
*COLD*

The conditions change on a regular basis that's why I wanted to put i
in another tab for the macro to lookup. The number of conditions ma
vary as well.

Moreover, kindly take note that the conditions are only part of a whol
partname. Example, the whole text in a cell is AD_COLD_XDRT123. Thu
this should not be deleted since the part name contains the strin
COLD.

I hope I gave a better explanation of my problem. Thank you in advanc
for your help.

rgds,
Ali

--
Ali
-----------------------------------------------------------------------
Alib's Profile: http://www.officehelp.in/member.php?userid=661
View this thread: http://www.officehelp.in/showthread.php?t=136770

Posted from - http://www.officehelp.i