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

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

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