Excel Macro help

[jjst34]

Hi,,
I am trying to write a line of code to do the following. Search in entire column A:A and look for ER600L and if it finds it to delete that entire row along w/ the next 8 rows. So far all I can do is make it highlight the row if it is the active cell w/ this code..

Sub processcells()
If ActiveCell = "ER600L" Then ActiveCell.EntireRow.Select
End Sub

It obviously needs some work and I can't get much further just yet... Thanks for any help!

[shahani]

Irn Duke should be around soon. He knows the answer.

[jjst34]

..

[jjst34]

Thanks a lot!!

Got one more if you guys don't mind.. I have a function that says the following:
=IF(LEFT($G14,5)="Total",SUM(I3:I13),"")

Is there a way to replace the above SUM(I3:I13) w/ something like a SUM(from cell just above this : to the final number in the range above)?? Sort of need it to do a ctrl+shift+up twice.

the first function works fine,, but the next time it finds "Total" say, in G55 this time, the #'s I need it to add are more than just the 11 columns the first function SUMS, so now I only get the total of I43:I54, but may need all the way up to I30.. Get what I'm looking for?

[Epidemic]

Sub biteme()
    Cells(1, 1).EntireColumn.Find("hello", LookIn:=xlWhole, Lookat:=xlWhole).Select
    Selection.EntireRow.Delete
End Sub

Well this is not exactly what i desired but it is close.

of course substitute you test value and column.

Cells (1,1) is the first cell first column then I use entire column to look through the all rows.

[jjst34]

Thanks.. This is close.. I need it to delete the following 9 rows also, not just the one w/ "hello" and also, how do I make it loop? It only finds one "hello" then I have to run it again.. Thanks a lot.. Any idea on the SUM question? Thanks guys!

[Epidemic]

=IF(G14="Total",SUM(OFFSET(E7,-4,4):OFFSET(E7,6,4)),"")

Not sure if this does what you want but here give this a whirl.

ps this is a formula of course.

I did not generate a macro for this one.

[Epidemic]

Originally posted by jjst34
Thanks.. This is close.. I need it to delete the following 9 rows also, not just the one w/ "hello" and also, how do I make it loop? It only finds one "hello" then I have to run it again.. Thanks a lot.. Any idea on the SUM question? Thanks guys!

you can put that directly in a loop as far as I am concerned

Perhaps a do while loop.

Sub biteme()
    On Error GoTo done
    Do While a < 1000
    Cells(1, 1).EntireColumn.Find("hello", LookIn:=xlWhole, Lookat:=xlWhole).Select
    Range(ActiveCell(), ActiveCell.Offset(-9, 0)).Select
    Selection.EntireRow.Delete
    a = 1 'this is just a bogus variable
    Loop
done:
End Sub

[Epidemic]

I put in the bogus variable because I could not think of what to loop until. I really just wanted to loop until there was an error then break out.

If the find finds nothing then you get an error and on that error you go to done:

I also notice that my -9 probably should be positive if you want to select the next 9 rows.

[jjst34]

Here is what I get when I run it... Not sure how to fix it...