Coding
 

For some reason the code does not work. Please check to see if code
covers my problem in words as follows:

I have about a hundred lines in an excel file. Some of the cells in
column B contain the letters abc.

What is the code to delete the rows with the letters abc in column B?





On Sun, 13 Aug 2006 19:29:11 -0600, "Dave Patrick"
wrote:

Here's one way;

For r = 100 To 1 Step -1
If LCase(Trim(Cells(r, 1).Text)) = "abc" Then
Cells(r & "," & r).EntireRow.Delete
End If
Next

Coding
 

For r = 100 To 1 Step -1
If LCase(Trim(Cells(r, 2).Text)) = "abc" Then
Cells(r & "," & r).EntireRow.Delete
End If
Next

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Ed" wrote:
| For some reason the code does not work. Please check to see if code
| covers my problem in words as follows:
|
| I have about a hundred lines in an excel file. Some of the cells in
| column B contain the letters abc.
|
| What is the code to delete the rows with the letters abc in column B?

Coding
 

Macro works too well.

Value I'm working with is " abc:", not"abc". Macro below won't work
with abc:.

I replaced abc: with abc in last six rows of worksheet and macro
worked. It deleted all rows.

I'm working on trying to fix it.

EJE



On Mon, 14 Aug 2006 07:23:53 -0600, "Dave Patrick"
wrote:

For r = 100 To 1 Step -1
If LCase(Trim(Cells(r, 2).Text)) = "abc" Then
Cells(r & "," & r).EntireRow.Delete
End If
Next

Coding
 

You can change "abc" that follows the equal sign to "abc:"

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Ed" wrote:
| Macro works too well.
|
| Value I'm working with is " abc:", not"abc". Macro below won't work
| with abc:.
|
| I replaced abc: with abc in last six rows of worksheet and macro
| worked. It deleted all rows.
|
| I'm working on trying to fix it.
|
| EJE

Coding
 

Still no luck. Here is the problem in words.

I down loaded to excel a list of purchases from a suppliers website
resulting in a table of six columns and 90 rows. Number of rows
increases over time.Columns are constant headed - Part# -
Description. - Quantity - Unit Price - Total Price - Requisitioner.

The problem is that the excel table Includes rows showing only
supplier information that I want to delete such as rows with only abc:
in one column, the rest of the row blank.

When I run your macro, allrows are deleted.






On Tue, 15 Aug 2006 20:52:34 -0600, "Dave Patrick"
wrote:

You can change "abc" that follows the equal sign to "abc:"

Coding
 

A thousand pardons..... my bad. Should have been;

For r = 10 To 1 Step -1
If LCase(Trim(Cells(r, 2).Text)) = "abc:" Then
Rows(r & ":" & r).EntireRow.Delete
End If
Next


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Ed" wrote:
| Still no luck. Here is the problem in words.
|
| I down loaded to excel a list of purchases from a suppliers website
| resulting in a table of six columns and 90 rows. Number of rows
| increases over time.Columns are constant headed - Part# -
| Description. - Quantity - Unit Price - Total Price - Requisitioner.
|
| The problem is that the excel table Includes rows showing only
| supplier information that I want to delete such as rows with only abc:
| in one column, the rest of the row blank.
|
| When I run your macro, allrows are deleted.

Coding
 

Thank you sir for your patience. I have the macro running exactly the
way I want it to. No problem deleting the rows with abc:

In addition to rows with abc: there were blank rows to be deleted
also.

I did have trouble with your exact coding so having a little
knowledge, I modified the code to the following:



For r = 122 To 1 Step -1
If Cells(r, 2).Text = "abc:" Or Cells(r, 2) = "" Then
Rows(r).EntireRow.Delete
End If
Next


The original table had 122 rows. Deleting abc: and blank rows left me
with 59 rows to manipulate.

I do appreciate all your help.

What do you think of my simplification of the code?

Thanks again,

Ed English





On Sat, 19 Aug 2006 15:09:51 -0600, "Dave Patrick"
wrote:

A thousand pardons..... my bad. Should have been;

For r = 10 To 1 Step -1
If LCase(Trim(Cells(r, 2).Text)) = "abc:" Then
Rows(r & ":" & r).EntireRow.Delete
End If
Next

Coding
 

Looks good to me.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Ed" wrote:
| Thank you sir for your patience. I have the macro running exactly the
| way I want it to. No problem deleting the rows with abc:
|
| In addition to rows with abc: there were blank rows to be deleted
| also.
|
| I did have trouble with your exact coding so having a little
| knowledge, I modified the code to the following:
|
|
|
| For r = 122 To 1 Step -1
| If Cells(r, 2).Text = "abc:" Or Cells(r, 2) = "" Then
| Rows(r).EntireRow.Delete
| End If
| Next
|
|
| The original table had 122 rows. Deleting abc: and blank rows left me
| with 59 rows to manipulate.
|
| I do appreciate all your help.
|
| What do you think of my simplification of the code?
|
| Thanks again,
|
| Ed English