Excel 2003 - Macro for deleting rows

[Solon]

Greetings,

I'm using Excel 2003 to do some deleting and sorting of a monthly list and
I'd like to automate most, if not all, of it.

Ideally, I'd want Excel to search through column D for a certain text entry
and delete any row that contains this text.

I would also like it to only search rows with data (anywhere from 50 to
about 3,000), but I'm sure searching the entire column won't be a big deal.

My difficulty seems, at this point, to be that I can't get the row to
delete. When I ran the macro, it deleted ALL rows, so I changed it to
Rows.Select and it selected ALL rows.

I can't seem to get Row.Delete (or Row.Select) to work on just the row where
the entry was found.

Here's what I've got so far:

Dim t As Range
Set t = Range("D23000")
For Each D In t
If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete
Next

It works, if I want to delete ALL rows (Select and Activate work as well).

I've never tried to get Excel to go to the last row with data and somehow
'record' the row number, much less use that to set a range, but I'd really
like to find a way to delete just one row based on the content of one of its
cells.

Any ideas?

Thanks,

Solon

 

[Ron de Bruin]

Start here Solon
http://www.rondebruin.nl/delete.htm

 

[Solon]

Ron,

Thanks for the info, wow that's a hefty bit of macro, isn't it?

Since I'm trying to learn more of this.. I played around a bit and came up
with this:

Dim FoundCell As Range
Dim myRng As Range

Set myRng = Range("D")

For Each D In myRng
Set FoundCell = myRng.Find("Desired Text Here")
If D.Value = ("Desired Text Here") Then FoundCell.EntireRow.Delete
Next


It works pretty well, but for some reason, only works on a few hundred rows,
then stops. I can run it a number of times in a row to clear everything out,
but am wondering why it will only work for so long, then stop.

Any ideas??

Thanks,

Solon

 

[Ron de Bruin]

For a Find macro see
http://www.rondebruin.nl/delete.htm#Find

 

[Gord Dibben]

Dim t As Range
Dim D As Range
Set t = Range("D23000")
For Each D In t
If D.Value = Worksheets("Sheet2").Range("A1").Value Then
D.Entirerow.Delete
End If
Next


Gord Dibben MS Excel MVP

 

[Solon]

AHHH.. the "D" before "Entirerow".. I WONDERED what was causing the
trouble..
I'm sort of getting a grasp of this.

Thanks,

Solon

 

[Ron de Bruin]

Note: When you delete you must start at the bottom row and go up

 

[Solon]

Yes, I'm learning THAT the hard way, too

Note: When you delete you must start at the bottom row and go up