Is there any formula like delete if?

S

Sasikiran

Hi,

I need a quick help in sorting out data.

I have a list of thousand email ids in each cell of the column A and among
them i need to delete data in some cells where a criteria matches.

Something like if the list has @xyz.com the entire value of the cell should
be deleted.

Please help.

Your quick response will be highly appreciated.

Thanks in advance.
 
M

Mike H

Hi,

You can't use a formula because a formula can only directly change the value
of the cell it is in. You could use a helper column and sort and then
manually delete or you could try this macro which works on column A. Right
click the sheet tab, view code and paste this in.

Sub stantial()
Dim CopyRange As Range
Dim MyRange As Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each C In MyRange
If InStr(1, UCase(C.Value), "@XYZ.COM") > 0 Then
If CopyRange Is Nothing Then
Set CopyRange = C
Else
Set CopyRange = Union(CopyRange, C)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.ClearContents
End If
End Sub

Mike
 
P

Pete_UK

You cannot delete data with a formula. However, you could use a
formula in a helper column to flag that a cell needs deleting, and
then apply autofilter on that column to list all those marked for
deletion and then delete the appropriate cells in one (manual)
operation. For example, assume your email addresses are in column A,
then put this formula in your helper column:

=IF(LEFT(A1,7)="xyz.com","Delete","")

and copy this down. Then apply autofilter to the helper column,
selecting Delete from the filter drop-down. Then highlight all the
visible cells in column A and press the <delete> key. Then select All
from the filter drop-down.

Hope this helps.

Pete
 
B

Brad

Use find and replace
Find
*xyz.com
replace

(leave blank)
This will replace all selected cell with blanks
 
Top