Excel 2003 - Macro for deleting rows
 

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("D2:D3000")
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

Excel 2003 - Macro for deleting rows
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Solon" wrote in message ...
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("D2:D3000")
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

Excel 2003 - Macro for deleting rows
 

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: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" wrote in message
...
Start here Solon
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Solon" wrote in message
...
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("D2:D3000")
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

Excel 2003 - Macro for deleting rows
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Solon" wrote in message ...
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: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" wrote in message
...
Start here Solon
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Solon" wrote in message
...
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("D2:D3000")
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

Excel 2003 - Macro for deleting rows
 

Dim t As Range
Dim D As Range
Set t = Range("D2:D3000")
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

On Fri, 7 Dec 2007 14:27:46 -0500, "Solon" wrote:

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("D2:D3000")
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

Excel 2003 - Macro for deleting rows
 

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

Thanks,

Solon


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Dim t As Range
Dim D As Range
Set t = Range("D2:D3000")
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

On Fri, 7 Dec 2007 14:27:46 -0500, "Solon"
wrote:

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("D2:D3000")
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

Excel 2003 - Macro for deleting rows
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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

Thanks,

Solon


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Dim t As Range
Dim D As Range
Set t = Range("D2:D3000")
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

On Fri, 7 Dec 2007 14:27:46 -0500, "Solon"
wrote:

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("D2:D3000")
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

Excel 2003 - Macro for deleting rows
 

Yes, I'm learning THAT the hard way, too :-)
"Ron de Bruin" wrote in message
...
Note: When you delete you must start at the bottom row and go up

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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

Thanks,

Solon


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Dim t As Range
Dim D As Range
Set t = Range("D2:D3000")
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

On Fri, 7 Dec 2007 14:27:46 -0500, "Solon"
wrote:

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("D2:D3000")
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