Hi Jim,

The goal, as you put it, it for one single delete.

I use those lines of code to delete ranges based on different
criteria. In the one above, it looks for the word Tribute in the range
of the column header Event ID.

In another instance I use the code to search for the text "Cash
Pending" under the column header Donation Status

In yet another instance I search for the text "Waived" or "Cancelled"
under the column header Registration Fee

It is entirely possible that one record (row of data) could meet
several of those criteria. Tribute/Cash Pending/Waived could all be in
the same row. Let's call that row 3, to help with the example. If I
use the delete code once, I would have Rng = 3, 3, 3... etc.

If I ran the delete at that point, row 3 would be deleted 3 times, 2
of those times may very well be valid records because as Excel
deletes, it moves the other rows up to fill the hole.

I'd like to perform the delete once, but prevent ranges from being
duplicated in Rng.

Hopefully this makes it a little more clear.


Steven

On Aug 21, 3:57*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Not really following your problem. The code you posted looks for a specific
event ID. It then search all of the cell in that column for the word Tribute.
Everywhere that tribute is found is added to a single large range which you
delete at the end... What is going on that it is deleting rows that you don't
intend to delete. Are there rows with tribute that you do not want to
delete??? Perhaps a better explanation of what you are up to, especially the
criteria for rows that you want to delete.

FYI if you are only deleting a few rows then individual deletes is not a big
deal but when you have to do a lot of deletes there is a distinct perfomance
improvement by doing a single large delete.
--
HTH...

Jim Thomlinson



" wrote:
Hi -

I'm using the following code in several places. Initially I had
simplified things by letting the Rng values pile up before passing the
Delete statement. Unfortunately, I realized that the various searches
I used occasionally came up with the same row for a different search
criteria. When I ran the delete, it deleted all the rows in the range,
but if a row appeared several times, it deleted valid rows as well.

This was originally a filter, but I thought performing one delete
rather than many would be better.

Thoughts?

Steven

* * * * * *Set FoundCell = SrcHdrRng.Find(What:="Event ID")

* * * * * *For Each MyCell In Range(FoundCell.Address, Cells(SrcLast,
FoundCell.Column).Address)

* * * * * * * * * * * * If MyCell.Value = "Tribute" Then
* * * * * * * * * * * * * * * * If Rng Is Nothing Then
* * * * * * * * * * * * * * * * * * Set Rng = Rows(MyCell.Row & ":" &
MyCell.Row)
* * * * * * * * * * * * * * * * Else: Set Rng = Union(Rng,
Rows(MyCell.Row & ":" & MyCell.Row))
* * * * * * * * * * * * * * * * End If
* * * * * * * * * * * * End If
* * * * * * Next
* * * * * * *If Not Rng Is Nothing Then
* * * * * * * *Rng.Delete
* * * * * * End If
* * * * * * Set Rng = Nothing- Hide quoted text -

- Show quoted text -