Search and destroy

Hi
I'm trying to write a macro to search for something then move one rwo and
then delete two rows.

I can do the search and the delete but I cannot do the move and the
selecting of two rows!

Any ideas please but I lose any more sleep?

Thanks
MB

Post your code so we have some idea of what technique you are using to
search and so forth.

--
regards,
Tom Ogilvy

"Pugwyrm" <[email protected]> wrote in message
news:[email protected]...
> Hi
> I'm trying to write a macro to search for something then move one rwo and
> then delete two rows.
>
> I can do the search and the delete but I cannot do the move and the
> selecting of two rows!
>
> Any ideas please but I lose any more sleep?
>
> Thanks
> MB

OK, this is what I have so far (and I've only got this far by Recording a
macro...)

Range("A1").Select
Cells.Find(What:="No History", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("4:5").Select
Selection.Delete Shift:=xlUp

Its the "Rows("4:5").Select that I need to automate as it won't always be
rows 4 and 5

Thanks

"Tom Ogilvy" wrote:

> Post your code so we have some idea of what technique you are using to
> search and so forth.
>
> --
> regards,
> Tom Ogilvy
>
> "Pugwyrm" <[email protected]> wrote in message
> news:[email protected]...
> > Hi
> > I'm trying to write a macro to search for something then move one rwo and
> > then delete two rows.
> >
> > I can do the search and the delete but I cannot do the move and the
> > selecting of two rows!
> >
> > Any ideas please but I lose any more sleep?
> >
> > Thanks
> > MB
>
>
>

Dim rng as Range
Range("A1").Select
set rng =Cells.Find(What:="No History", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
if not rng is nothing then
rng.offset(2,0).Resize(2).EntireRow.Delete
End If

if there will be multiple occurances of "No History"

Dim rng as Range
Range("A1").Select
set rng =Cells.Find(What:="No History", _
After:=Range("A65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
if not rng is nothing then
frow = rng.row
do
rng.offset(2,0).Resize(2).EntireRow.Delete
set rng = cell.findnext(rng)
Loop while rng.row > fRow
End If


"Pugwyrm" <[email protected]> wrote in message
news:[email protected]...
> OK, this is what I have so far (and I've only got this far by Recording a
> macro...)
>
> Range("A1").Select
> Cells.Find(What:="No History", After:=ActiveCell, LookIn:=xlFormulas,
_
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
> Rows("4:5").Select
> Selection.Delete Shift:=xlUp
>
> Its the "Rows("4:5").Select that I need to automate as it won't always be
> rows 4 and 5
>
> Thanks
>
> "Tom Ogilvy" wrote:
>
> > Post your code so we have some idea of what technique you are using to
> > search and so forth.
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> > "Pugwyrm" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi
> > > I'm trying to write a macro to search for something then move one rwo
and
> > > then delete two rows.
> > >
> > > I can do the search and the delete but I cannot do the move and the
> > > selecting of two rows!
> > >
> > > Any ideas please but I lose any more sleep?
> > >
> > > Thanks
> > > MB
> >
> >
> >

Cheers, it's a start!

"Tom Ogilvy" wrote:

> Dim rng as Range
> Range("A1").Select
> set rng =Cells.Find(What:="No History", After:=ActiveCell,
> LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False)
> if not rng is nothing then
> rng.offset(2,0).Resize(2).EntireRow.Delete
> End If
>
> if there will be multiple occurances of "No History"
>
> Dim rng as Range
> Range("A1").Select
> set rng =Cells.Find(What:="No History", _
> After:=Range("A65536"), _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False, _
> SearchFormat:=False)
> if not rng is nothing then
> frow = rng.row
> do
> rng.offset(2,0).Resize(2).EntireRow.Delete
> set rng = cell.findnext(rng)
> Loop while rng.row > fRow
> End If
>
>
> "Pugwyrm" <[email protected]> wrote in message
> news:[email protected]...
> > OK, this is what I have so far (and I've only got this far by Recording a
> > macro...)
> >
> > Range("A1").Select
> > Cells.Find(What:="No History", After:=ActiveCell, LookIn:=xlFormulas,
> _
> > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > MatchCase:=False, SearchFormat:=False).Activate
> > Rows("4:5").Select
> > Selection.Delete Shift:=xlUp
> >
> > Its the "Rows("4:5").Select that I need to automate as it won't always be
> > rows 4 and 5
> >
> > Thanks
> >
> > "Tom Ogilvy" wrote:
> >
> > > Post your code so we have some idea of what technique you are using to
> > > search and so forth.
> > >
> > > --
> > > regards,
> > > Tom Ogilvy
> > >
> > > "Pugwyrm" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi
> > > > I'm trying to write a macro to search for something then move one rwo
> and
> > > > then delete two rows.
> > > >
> > > > I can do the search and the delete but I cannot do the move and the
> > > > selecting of two rows!
> > > >
> > > > Any ideas please but I lose any more sleep?
> > > >
> > > > Thanks
> > > > MB
> > >
> > >
> > >
>
>
>