Deleting Unneeded Records

B

Bill Foley

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!
 
D

Don Guillett

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
 
N

Norman Jones

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.
 
B

Bill Foley

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!
 
B

Bill Foley

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.
 
D

Don Guillett

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
 
B

Bill Foley

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)?
 
C

Calligra

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!
 
B

Bill Foley

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
 
D

Don Guillett

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
 
T

Tom Ogilvy

Dim rng as Range
set rng = Range(Range("A2"),Range("A2").End(xldown))
rng.offset(0,12).Formula = "=OR(G2=""Power"",H2=""Power"")"
 
B

Bill Foley

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!
 
D

Don Guillett

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
 
T

Tom Ogilvy

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).
 
T

Tom Ogilvy

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.
 
Top