delete all lines after finding criteria

[GaiGauci]

Hi all, I seem to be asking lots of questions today- I think I must have run
over a black cat!

I tried to modify some code I found to delete all rows after finding the
words "end of report". I download a report to SAP and I am trying to tidy it
up.

I tested the code and it worked so I cut it into a much larger code that
does a whole lot of things, this being just one of. Here's the code I used:

'delete lines after the word "End of Report"

fr = Columns(1).Find("End Of Report").Row
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows(fr & ":" & lr).Delete

It comes up wtih a run time error 91- object required.

I am only self taught - or should I say user group taught. I just don't get
the whole object thing despite trying to read the manual. I know I have to
set the variable for fr and lr. lr =lastrow and I think I can just replace lr
with Lastrow, however, what do I do for the first row of the range (i.e. fr)?

Any help would be appreciated. I feel quite dumb not getting it.

Cheers
Gai

 

[OssieMac]

Hi Gai,

The code you have will only work on the active sheet. And yes! it does work.

However, my guess is that you inserted the code between a With/End With and
you did not place the leading stop to tell the code that it belongs to the
With statement and it is trying to access it on the active sheet and not the
sheet referred to by the With statement.

In longhand your code could be re-written like the following to include the
worksheet name which is understood for the active sheet but is required if
the reference is to a sheet other than the active sheet. (It is actually 3
lines of code and the lines may break in this post.)

fr = Sheets("Sheet1").Columns(1).Find("End Of Report").Row

lr = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "a").End(xlUp).Row

Sheets("Sheet1").Rows(fr & ":" & lr).Delete



However, if you use With/End With then like the following.
With Sheets("Sheet1")
fr = .Columns(1).Find("End Of Report").Row
lr = .Cells(.Rows.Count, "a").End(xlUp).Row
.Rows(fr & ":" & lr).Delete
End With

Note the leading dot with .Columns , .Cells,. Rows.Count and .Rows that
tell the code it belongs to the With Sheets("Sheet1")

Hope it helps.

 

[GaiGauci]

Thanks to OssieMac and Joel. Both worked. Yes, the macro didn't find "End of
Report" beacause it should have been looking for "ObjectType" (too many late
nights!) and yes it did need a With statement. Thanks to both for your help.
Another little bit closer now to perhaps being less of a goose but I think I
still have a long way to go

Cheers
Gai