Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default delete all lines after finding criteria

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default delete all lines after finding criteria


You probably aren't finding anything . try this



set c = Columns(1).Find(what:="End Of Report", _
lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then
fr = c.row
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows(fr & ":" & lr).Delete
end if


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167691

Microsoft Office Help

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default delete all lines after finding criteria

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.

--
Regards,

OssieMac


"GaiGauci" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default delete all lines after finding criteria

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
"joel" wrote:


You probably aren't finding anything . try this



set c = Columns(1).Find(what:="End Of Report", _
lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then
fr = c.row
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows(fr & ":" & lr).Delete
end if


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167691

Microsoft Office Help

.

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default delete all lines after finding criteria


joel;604685 Wrote:
You probably aren't finding anything . try this



set c = Columns(1).Find(what:="End Of Report", _
lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then
fr = c.row
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows(fr & ":" & lr).Delete
end if


I would like to use this (or something similar) but to delete all rows
ABOVE my criteria (End Of Report would be replace with Cash Receipts and
will always be in column A)...can this code be modified to move up one
row from my criteria and deleted all rows from 1 to ??

Thanks in advance Bg:)


--
Trixie

~TRIXIE
------------------------------------------------------------------------
Trixie's Profile: 438
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167691

Microsoft Office Help

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Keep lines when one column equals certain criteria Donna[_2_] Excel Worksheet Functions 2 April 23rd 10 01:16 AM
FInding Min Value with Criteria AndrewK Excel Worksheet Functions 11 August 11th 09 07:56 AM
Finding the value for the point two lines cross Brian Charts and Charting in Excel 1 March 3rd 06 12:59 AM
Counting lines that meet TWO criteria sam Excel Worksheet Functions 2 January 31st 06 10:04 PM
Sum for lines equal to criteria rfhorn Excel Worksheet Functions 5 April 28th 05 01:55 PM


All times are GMT +1. The time now is 10:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"