Thanks a lot, Deepak.
Here's the file:
http://groups.google.com/group/excel-macros/web/Test_DeleteByNumberName.xls
(I'm under an NDA, so I had to strip it of any meaningful information,
but there are report numbers there for illustration).
Two of the reports have the same "Number", called "In Folder X", but
different names.
On the Reports No Longer Needed tab, I list the report numbers of some
reports to be deleted, and for the report where the number wouldn't be
uniquely identifying, I list the report number and the report title.
If a report title isn't listed, then it can just delete it solely by
the report number. If a report number and title are listed, then it
must delete only the row that matches both.
Thanks for offering to help!
On Jun 8, 2:59 pm, Deepak Rai <daksh1...@gmail.com> wrote:
> Hi,
>
> Give me a sample file I will solve this for you.
>
> Regards,
>
> Deepak
>
>
>
> On Tue, Jun 8, 2010 at 8:35 PM, molinari <dh0...@gmail.com> wrote:
> > I have a list of reports to be printed out Monday - Friday, where each
> > worksheet is Monday, Tuesday, Wednesday, Thursday, or Friday. I made
> > another worksheet to list reports that we no longer need (by their
> > report number). I want the macro to go through the worksheet and
> > delete the rows that correspond to these reports, but I'm getting
> > stuck at the find step: it keeps giving me this error about "Object or
> > variable with block variable not set".
>
> > How do I fix this? (then afterwards, how do I tell it to delete the
> > rows where it found something, but not delete the row its on if it
> > finds nothing?)
>
> > Sub Macro5()
> > '
> > ' Macro5 Macro
> > ' Macro recorded 6/8/2010 by David J. Heinrich
>
> > Dim y As Byte
> > Sheets("Reports Not Needed").Select
> > y = xlLastRow ' y = numbers of rows
>
> > Dim ReportsNotNeeded() As Variant
> > ReportsNotNeeded = Range(Cells(2, 2), Cells(y, 2)).Value
>
> > Dim ws As Worksheet
> > Dim x As Byte
>
> > For Each ws In Worksheets
> > Sheets(ws.Name).Select
> > For x = 1 To y - 1
>
> > Range("B3:B1391").Select
> > Selection.Find( _
> > What:=ReportsNotNeeded(x, 1), _
> > After:=Range("B3"), _
> > LookIn:=xlValues, _
> > LookAt:=xlWhole, _
> > SearchOrder:=xlByColumns, _
> > SearchDirection:=xlNext, _
> > MatchCase:=False, _
> > SearchFormat:=False).Activate
>
> > Next x
>
> > Next ws
>
> > End Sub
>
> > --
>
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 7000 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
