Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Hey Gang,
I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Hi Bill,
Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
I'll give it a go. Thanks, Don!
-- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Don Guillett" wrote in message ... something like this. UNTESTED for i = cells(rows.count,"g"),end(xlup).row to 2 step -1 if cells(i,"g")<"Power" and cells(i,"h")< "Power" then cells(i,"g").entire row delete next -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Another great idea! I was running into some syntax problems working with
the code Don provided (hence, UNTESTED). I'll try this method as well and report back. THANKS! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Don,
Still having some problems with your sample code. Here is what I have so far: Sub DeleteUnneededRows() Dim i As Integer For i = Cells(Rows.Count, "g").End(xlUp).Row To 2 Step -1 If Cells(i, "g") < "Power" Or Cells(i, "h") < "Power" Then Cells(i, "g").EntireRow.Delete Next End Sub I should end up with around 120 (of 650) records, but I end up with only one record and it doesn't even have "Power" in column "G" or "H". While attempting to learn as I go, I am confused as to what the "g" in "For i = Cells(Rows.Count, "g")" and the "g" in "Cells(i, "g").EntireRow.Delete". Any clarification would be greatly appreciated. -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Don Guillett" wrote in message ... something like this. UNTESTED for i = cells(rows.count,"g"),end(xlup).row to 2 step -1 if cells(i,"g")<"Power" and cells(i,"h")< "Power" then cells(i,"g").entire row delete next -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
try
Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Another great idea! I was running into some syntax problems working with the code Don provided (hence, UNTESTED). I'll try this method as well and report back. THANKS! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
This does work, but since I am trying to automate this process, I need to be
able to autofill the entire column with this formula since the number of records will change each month. Any clues as to some code to look at the range in say Column "A" (starting at A2) and copy this formula down in Column M (also starting at M2)? -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Try the following:
Dim iMinCol as integer Dim iMaxCol as integer Dim i as integer iMinCol = 1 iMaxCol = n For i = iMinCol to iMaxCol If Sheet1.Cells(1, i).Value = "Power" Then Sheet1.Columns(i).Delete Exit For End If Next i *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Had to end the "End If", but this does work. THANKS!
Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete End If Next i End Sub -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Don Guillett" wrote in message ... try Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Another great idea! I was running into some syntax problems working with the code Don provided (hence, UNTESTED). I'll try this method as well and report back. THANKS! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Had you put the if statement on one line then the end if would NOT have been
necessary. Or use a continuation line to break the line such as Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And _ UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Had to end the "End If", but this does work. THANKS! Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete End If Next i End Sub -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Don Guillett" wrote in message ... try Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Another great idea! I was running into some syntax problems working with the code Don provided (hence, UNTESTED). I'll try this method as well and report back. THANKS! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Dim rng as Range
set rng = Range(Range("A2"),Range("A2").End(xldown)) rng.offset(0,12).Formula = "=OR(G2=""Power"",H2=""Power"")" -- Regards, Tom Ogilvy "Bill Foley" wrote in message ... This does work, but since I am trying to automate this process, I need to be able to autofill the entire column with this formula since the number of records will change each month. Any clues as to some code to look at the range in say Column "A" (starting at A2) and copy this formula down in Column M (also starting at M2)? -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Thanks for all the help. I ended up using Don's code, but have made note of
Tom.s, Calligra's, and Norman's ideas as well! I certainly appreciate the help! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Another way so you can see that excel skins cats in many ways
Sub delifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 'one line below If Application.CountIf(Range("g" & i & ":h" & i), "power") < 1 Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Just an addendum
And if you had posted it like that originally, then it wouldn't have looked like it was on two separate lines (due to wordwrap). -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... Had you put the if statement on one line then the end if would NOT have been necessary. Or use a continuation line to break the line such as Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And _ UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Had to end the "End If", but this does work. THANKS! Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete End If Next i End Sub -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Don Guillett" wrote in message ... try Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Another great idea! I was running into some syntax problems working with the code Don provided (hence, UNTESTED). I'll try this method as well and report back. THANKS! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
Point well taken but the vbe should have make it red (or selected color)
-- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... Just an addendum And if you had posted it like that originally, then it wouldn't have looked like it was on two separate lines (due to wordwrap). -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... Had you put the if statement on one line then the end if would NOT have been necessary. Or use a continuation line to break the line such as Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And _ UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Had to end the "End If", but this does work. THANKS! Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete End If Next i End Sub -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Don Guillett" wrote in message ... try Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Another great idea! I was running into some syntax problems working with the code Don provided (hence, UNTESTED). I'll try this method as well and report back. THANKS! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Unneeded Records
No, because there are no syntax errors in any single command/line. It is
only when compiled that VBA recognizes that the constructs are unbalanced. Pasting the code from your original posting displayed no highlighting. -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... Point well taken but the vbe should have make it red (or selected color) -- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... Just an addendum And if you had posted it like that originally, then it wouldn't have looked like it was on two separate lines (due to wordwrap). -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... Had you put the if statement on one line then the end if would NOT have been necessary. Or use a continuation line to break the line such as Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And _ UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Had to end the "End If", but this does work. THANKS! Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete End If Next i End Sub -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Don Guillett" wrote in message ... try Sub deleteifnopower() For i = Cells(Rows.Count, "g").End(xlUp).Row To 1 Step -1 If UCase(Cells(i, "g")) < "POWER" And UCase(Cells(i, "h")) < "POWER" Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Bill Foley" wrote in message ... Another great idea! I was running into some syntax problems working with the code Don provided (hence, UNTESTED). I'll try this method as well and report back. THANKS! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm "Norman Jones" wrote in message ... Hi Bill, Add a helper column (say column M ) to your data and insert the formula: =OR(G2="Power",H2="Power") in cell M2 and drag this down to the last row of data. Then autofilter your data using TRUE as your criterion in column M. This should return all the data you want to delete. --- Regards, Norman "Bill Foley" wrote in message ... Hey Gang, I am trying something new and need some assistance. I get a new workbook each month with several hundred records on one worksheet. I have written the simple macros to do the following: 1. Create a new WorkSheet 2. Copy all data from Sheet1 to Sheet2 3. Delete the unneeded columns 4. Autofit the remaining columns The last thing I need to do is to delete all rows where columns "G" or "H" do not include the word "Power". I can't simply autofilter because I need all records where "either" columns have "Power" in it. Any code idea? I am sure it is simple, but my VBA use in Excel is much less than my knowledge in PowerPoint or Word (and that is somewhat limited)! HA! TIA! -- Bill Foley, Microsoft MVP (PowerPoint) Microsoft Office Specialist Master Instructor - XP www.pttinc.com Check out PPT FAQs at: http://www.rdpslides.com/pptfaq/ Check out Word FAQs at: http://word.mvps.org/FAQs/General/index.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Duplicate records | Excel Discussion (Misc queries) | |||
Deleting duplicate records | Excel Discussion (Misc queries) | |||
Deleting unneeded rows and columns | Excel Discussion (Misc queries) | |||
Deleting specific records | Excel Discussion (Misc queries) | |||
Deleting records ADO | Excel Programming |