Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep lines when one column equals certain criteria | Excel Worksheet Functions | |||
FInding Min Value with Criteria | Excel Worksheet Functions | |||
Finding the value for the point two lines cross | Charts and Charting in Excel | |||
Counting lines that meet TWO criteria | Excel Worksheet Functions | |||
Sum for lines equal to criteria | Excel Worksheet Functions |