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

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

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

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

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

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

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

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

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

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

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

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!

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

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