Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming | |||
Need coding help. | Excel Programming | |||
VBA Coding (?) | Excel Programming | |||
Could someone help me with some coding | Excel Programming |