Can you search for patterns?

  • Thread starter Thread starter Tony Corneto
  • Start date Start date
T

Tony Corneto

First... thanks to everyone who has helped me in in the
past. You've all saved me hours and hours of work. This
is a wonderful forum.

Here's my latest question:

I have a single long column of data. In decending order
it goes: Name then Location then Email then space and
repeats this pattern over a few thousand cells.
My problem is I need to delete the groups that don't have
an email address associated with it. That pattern goes:
Name then Location then space.
Since I'm searching for the NON existence of an item I'm
not sure how to proceed. The only thing that is unique
about what I am searching for is the pattern: cell cell
blank instead of cell cell cell blank.
Is there a way to search for only the a two cell pattern
so I can delete when found? I'm sure there is a way to
do this but am stumped.

Thanks in advance,

Tony
 
Hi

Hold down ALT-F11 and go to Insert>Module and in the blank space paste
the following code:

*************************************************

Sub DeleteNoEmail()

Dim i As Integer
Dim cellCount As Single
cellCount = 0

Do While i >= 0
If Not IsEmpty(ActiveCell.Offset(i, 0)) Then
cellCount = cellCount + 1
If cellCount = 3 Then cellCount = 0
Else
If IsEmpty(ActiveCell.Offset(i + 1, 0)) Then
i = -2
End If
If cellCount = 2 Then
ActiveCell.Offset(i, 0).EntireRow.Delete
ActiveCell.Offset(i - 1, 0).EntireRow.Delete
ActiveCell.Offset(i - 2, 0).EntireRow.Delete
cellCount = 0
i = i - 3
End If
End If
i = i + 1

Loop

End Sub

*********************************************
(watch line wrap)

Click the first data cell of your column (ie. Name) and go to
Tools>Macro>Run Macro and click on DeleteNoEmails and click Run.

This works for me using test data on a short range so make a backup of
the file first before running the macro.

The macro deletes the groups with no emails.

Tom
 
Back
Top