I am copying a text file into excel (all one column A) from a database and
want to delete rows that have the following information:
the date for example 08/14/2008 followed by QMS Log Page 69 of 79 (this will
of course be 1 of 79 etc). I have tried to use some of the threads seen in
this discussion group in a macro but with no luck. My attempt is
Sub testme02()

Dim MyRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long

myStrings = Array("QMS Log Page") 'add more strings if you need

Set wks = ActiveSheet

With wks
Set MyRng = .Range("a2:a" & .Rows.Count)
End With

For iCtr = LBound(myStrings) To UBound(myStrings)
Do
With MyRng
Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
End With
Loop
Next iCtr
End Sub

look at findnext in vb help.

--


Gary


"gbpg" <(E-Mail Removed)> wrote in message
news:B5CEBD18-20D8-4948-BC2D-(E-Mail Removed)...
>I am copying a text file into excel (all one column A) from a database and
> want to delete rows that have the following information:
> the date for example 08/14/2008 followed by QMS Log Page 69 of 79 (this will
> of course be 1 of 79 etc). I have tried to use some of the threads seen in
> this discussion group in a macro but with no luck. My attempt is
> Sub testme02()
>
> Dim MyRng As Range
> Dim FoundCell As Range
> Dim wks As Worksheet
> Dim myStrings As Variant
> Dim iCtr As Long
>
> myStrings = Array("QMS Log Page") 'add more strings if you need
>
> Set wks = ActiveSheet
>
> With wks
> Set MyRng = .Range("a2:a" & .Rows.Count)
> End With
>
> For iCtr = LBound(myStrings) To UBound(myStrings)
> Do
> With MyRng
> Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
> after:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> lookat:=xlWhole, _
> searchorder:=xlByRows, _
> searchdirection:=xlNext, _
> MatchCase:=False)
>
> If FoundCell Is Nothing Then
> Exit Do
> Else
> FoundCell.EntireRow.Delete
> End If
> End With
> Loop
> Next iCtr
> End Sub
>

Sorry that does not help

"Gary Keramidas" wrote:

> look at findnext in vb help.
>
> --
>
>
> Gary
>
>
> "gbpg" <(E-Mail Removed)> wrote in message
> news:B5CEBD18-20D8-4948-BC2D-(E-Mail Removed)...
> >I am copying a text file into excel (all one column A) from a database and
> > want to delete rows that have the following information:
> > the date for example 08/14/2008 followed by QMS Log Page 69 of 79 (this will
> > of course be 1 of 79 etc). I have tried to use some of the threads seen in
> > this discussion group in a macro but with no luck. My attempt is
> > Sub testme02()
> >
> > Dim MyRng As Range
> > Dim FoundCell As Range
> > Dim wks As Worksheet
> > Dim myStrings As Variant
> > Dim iCtr As Long
> >
> > myStrings = Array("QMS Log Page") 'add more strings if you need
> >
> > Set wks = ActiveSheet
> >
> > With wks
> > Set MyRng = .Range("a2:a" & .Rows.Count)
> > End With
> >
> > For iCtr = LBound(myStrings) To UBound(myStrings)
> > Do
> > With MyRng
> > Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
> > after:=.Cells(.Cells.Count), _
> > LookIn:=xlValues, _
> > lookat:=xlWhole, _
> > searchorder:=xlByRows, _
> > searchdirection:=xlNext, _
> > MatchCase:=False)
> >
> > If FoundCell Is Nothing Then
> > Exit Do
> > Else
> > FoundCell.EntireRow.Delete
> > End If
> > End With
> > Loop
> > Next iCtr
> > End Sub
> >
>
>
>

On Aug 15, 11:53*am, gbpg <g...@discussions.microsoft.com> wrote:
> Sorry that does not help
>
> "Gary Keramidas" wrote:
> > look at findnext in vb help.
>
> > --
>
> > Gary
>
> > "gbpg" <g...@discussions.microsoft.com> wrote in message
> >news:B5CEBD18-20D8-4948-BC2D-(E-Mail Removed)...
> > >I am copying a text file into excel (all one column A) from a databaseand
> > > want to delete rows that have the following information:
> > > the date for example 08/14/2008 followed by QMS Log Page 69 of 79 (this will
> > > of course be 1 of 79 etc). I have tried to use some of the threads seen in
> > > this discussion group in a macro but with no luck. My attempt is
> > > Sub testme02()
>
> > > * *Dim MyRng As Range
> > > * *Dim FoundCell As Range
> > > * *Dim wks As Worksheet
> > > * *Dim myStrings As Variant
> > > * *Dim iCtr As Long
>
> > > * *myStrings = Array("QMS Log Page") 'add more strings if you need
>
> > > * *Set wks = ActiveSheet
>
> > > * *With wks
> > > * * * *Set MyRng = .Range("a2:a" & .Rows.Count)
> > > * *End With
>
> > > * *For iCtr = LBound(myStrings) To UBound(myStrings)
> > > * * * *Do
> > > * * * * * *With MyRng
> > > * * * * * * * *Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
> > > * * * * * * * * * * * * * * * * * *after:=.Cells(.Cells.Count), _
> > > * * * * * * * * * * * * * * * * * *LookIn:=xlValues, _
> > > * * * * * * * * * * * * * * * * * *lookat:=xlWhole, _
> > > * * * * * * * * * * * * * * * * * *searchorder:=xlByRows, _
> > > * * * * * * * * * * * * * * * * * *searchdirection:=xlNext, _
> > > * * * * * * * * * * * * * * * * * *MatchCase:=False)
>
> > > * * * * * * * *If FoundCell Is Nothing Then
> > > * * * * * * * * * *Exit Do
> > > * * * * * * * *Else
> > > * * * * * * * * * *FoundCell.EntireRow.Delete
> > > * * * * * * * *End If
> > > * * * * * *End With
> > > * * * *Loop
> > > * *Next iCtr
> > > End Sub

Try this...

Dim MyRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long

myStrings = Array("QMS Log Page") 'add more strings if you need

Set wks = ActiveSheet

With wks
Set MyRng = .Range("a2:a" & .Rows.Count)
End With

For iCtr = LBound(myStrings) To UBound(myStrings)
Do
With MyRng
Set FoundCell = .Cells.Find what:= "*" &
myStrings(iCtr) & "*" ' this will find anything that contains your
sting
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
End With
Loop
Next iCtr
End Sub

Looking at your original code there were two lines that jumped out...

lookat:=xlWhole - You said your string starts with a date then QMS
etc. this would only find those cells that match exactly "QMS Log
Page"
after:=.Cells(.Cells.Count) - I am not sure what the point of this is?

James

try changing this:
lookat:=xlWhole

to this:

lookat:=xlPart


--


Gary


"gbpg" <(E-Mail Removed)> wrote in message
news:025B9C1A-5D38-4EDD-81A3-(E-Mail Removed)...
> Sorry that does not help
>
> "Gary Keramidas" wrote:
>
>> look at findnext in vb help.
>>
>> --
>>
>>
>> Gary
>>
>>
>> "gbpg" <(E-Mail Removed)> wrote in message
>> news:B5CEBD18-20D8-4948-BC2D-(E-Mail Removed)...
>> >I am copying a text file into excel (all one column A) from a database and
>> > want to delete rows that have the following information:
>> > the date for example 08/14/2008 followed by QMS Log Page 69 of 79 (this
>> > will
>> > of course be 1 of 79 etc). I have tried to use some of the threads seen in
>> > this discussion group in a macro but with no luck. My attempt is
>> > Sub testme02()
>> >
>> > Dim MyRng As Range
>> > Dim FoundCell As Range
>> > Dim wks As Worksheet
>> > Dim myStrings As Variant
>> > Dim iCtr As Long
>> >
>> > myStrings = Array("QMS Log Page") 'add more strings if you need
>> >
>> > Set wks = ActiveSheet
>> >
>> > With wks
>> > Set MyRng = .Range("a2:a" & .Rows.Count)
>> > End With
>> >
>> > For iCtr = LBound(myStrings) To UBound(myStrings)
>> > Do
>> > With MyRng
>> > Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
>> > after:=.Cells(.Cells.Count), _
>> > LookIn:=xlValues, _
>> > lookat:=xlWhole, _
>> > searchorder:=xlByRows, _
>> > searchdirection:=xlNext, _
>> > MatchCase:=False)
>> >
>> > If FoundCell Is Nothing Then
>> > Exit Do
>> > Else
>> > FoundCell.EntireRow.Delete
>> > End If
>> > End With
>> > Loop
>> > Next iCtr
>> > End Sub
>> >
>>
>>
>>

On Aug 15, 12:25*pm, james.bi...@gmail.com wrote:
> On Aug 15, 11:53*am, gbpg <g...@discussions.microsoft.com> wrote:
>
>
>
> > Sorry that does not help
>
> > "Gary Keramidas" wrote:
> > > look at findnext in vb help.
>
> > > --
>
> > > Gary
>
> > > "gbpg" <g...@discussions.microsoft.com> wrote in message
> > >news:B5CEBD18-20D8-4948-BC2D-(E-Mail Removed)...
> > > >I am copying a text file into excel (all one column A) from a database and
> > > > want to delete rows that have the following information:
> > > > the date for example 08/14/2008 followed by QMS Log Page 69 of 79 (this will
> > > > of course be 1 of 79 etc). I have tried to use some of the threads seen in
> > > > this discussion group in a macro but with no luck. My attempt is
> > > > Sub testme02()
>
> > > > * *Dim MyRng As Range
> > > > * *Dim FoundCell As Range
> > > > * *Dim wks As Worksheet
> > > > * *Dim myStrings As Variant
> > > > * *Dim iCtr As Long
>
> > > > * *myStrings = Array("QMS Log Page") 'add more strings if youneed
>
> > > > * *Set wks = ActiveSheet
>
> > > > * *With wks
> > > > * * * *Set MyRng = .Range("a2:a" & .Rows.Count)
> > > > * *End With
>
> > > > * *For iCtr = LBound(myStrings) To UBound(myStrings)
> > > > * * * *Do
> > > > * * * * * *With MyRng
> > > > * * * * * * * *Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
> > > > * * * * * * * * * * * * * * * * **after:=.Cells(.Cells.Count), _
> > > > * * * * * * * * * * * * * * * * **LookIn:=xlValues, _
> > > > * * * * * * * * * * * * * * * * **lookat:=xlWhole, _
> > > > * * * * * * * * * * * * * * * * **searchorder:=xlByRows, _
> > > > * * * * * * * * * * * * * * * * **searchdirection:=xlNext, _
> > > > * * * * * * * * * * * * * * * * **MatchCase:=False)
>
> > > > * * * * * * * *If FoundCell Is Nothing Then
> > > > * * * * * * * * * *Exit Do
> > > > * * * * * * * *Else
> > > > * * * * * * * * * *FoundCell.EntireRow.Delete
> > > > * * * * * * * *End If
> > > > * * * * * *End With
> > > > * * * *Loop
> > > > * *Next iCtr
> > > > End Sub
>
> Try this...
>
> * * Dim MyRng As Range
> * * Dim FoundCell As Range
> * * Dim wks As Worksheet
> * * Dim myStrings As Variant
> * * Dim iCtr As Long
>
> * * myStrings = Array("QMS Log Page") 'add more strings if you need
>
> * * Set wks = ActiveSheet
>
> * * With wks
> * * * * Set MyRng = .Range("a2:a" & .Rows.Count)
> * * End With
>
> * * For iCtr = LBound(myStrings) To UBound(myStrings)
> * * * * Do
> * * * * * * With MyRng
> * * * * * * * * Set FoundCell = .Cells.Find what:= "*" &
> myStrings(iCtr) & "*" ' this will find anything that contains your
> sting
> * * * * * * * * If FoundCell Is Nothing Then
> * * * * * * * * * * Exit Do
> * * * * * * * * Else
> * * * * * * * * * * FoundCell.EntireRow.Delete
> * * * * * * * * End If
> * * * * * * End With
> * * * * Loop
> * * Next iCtr
> End Sub
>
> Looking at your original code there were two lines that jumped out...
>
> lookat:=xlWhole - You said your string starts with a date then QMS
> etc. this would only find those cells that match exactly "QMS Log
> Page"
> after:=.Cells(.Cells.Count) - I am not sure what the point of this is?
>
> James

Thinking about it would it not be easier just to use the autofilter?

Dim MyRng As Range
Dim FoundCell As Range
Dim wks As Worksheet
Dim myStrings As Variant
Dim iCtr As Long

myStrings = Array("QMS Log Page") 'add more strings if you need

Set wks = ActiveSheet

With wks
Set MyRng = .Range("a2:a" & .Rows.Count)
set MyDeleteRng = .Range("a3:a" & .rows.count) ' This assumes
header information in row 2?
End With

For iCtr = LBound(myStrings) To UBound(myStrings)
MyRng.Autofilter 1, "*" myStrings(iCtr) & "*"
MyDeleteRng.entirerow.delete
Next iCtr
myRng.AutofIlter ' switch the autofilter off
End Sub

James

On Aug 15, 12:32*pm, james.bi...@gmail.com wrote:
> On Aug 15, 12:25*pm, james.bi...@gmail.com wrote:
>
>
>
> > On Aug 15, 11:53*am, gbpg <g...@discussions.microsoft.com> wrote:
>
> > > Sorry that does not help
>
> > > "Gary Keramidas" wrote:
> > > > look at findnext in vb help.
>
> > > > --
>
> > > > Gary
>
> > > > "gbpg" <g...@discussions.microsoft.com> wrote in message
> > > >news:B5CEBD18-20D8-4948-BC2D-(E-Mail Removed)...
> > > > >I am copying a text file into excel (all one column A) from a database and
> > > > > want to delete rows that have the following information:
> > > > > the date for example 08/14/2008 followed by QMS Log Page 69 of 79(this will
> > > > > of course be 1 of 79 etc). I have tried to use some of the threads seen in
> > > > > this discussion group in a macro but with no luck. My attempt is
> > > > > Sub testme02()
>
> > > > > * *Dim MyRng As Range
> > > > > * *Dim FoundCell As Range
> > > > > * *Dim wks As Worksheet
> > > > > * *Dim myStrings As Variant
> > > > > * *Dim iCtr As Long
>
> > > > > * *myStrings = Array("QMS Log Page") 'add more strings if you need
>
> > > > > * *Set wks = ActiveSheet
>
> > > > > * *With wks
> > > > > * * * *Set MyRng = .Range("a2:a" & .Rows.Count)
> > > > > * *End With
>
> > > > > * *For iCtr = LBound(myStrings) To UBound(myStrings)
> > > > > * * * *Do
> > > > > * * * * * *With MyRng
> > > > > * * * * * * * *Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _
> > > > > * * * * * * * * * * * * * * * * * *after:=.Cells(.Cells.Count), _
> > > > > * * * * * * * * * * * * * * * * * *LookIn:=xlValues, _
> > > > > * * * * * * * * * * * * * * * * * *lookat:=xlWhole, _
> > > > > * * * * * * * * * * * * * * * * * *searchorder:=xlByRows, _
> > > > > * * * * * * * * * * * * * * * * * *searchdirection:=xlNext, _
> > > > > * * * * * * * * * * * * * * * * * *MatchCase:=False)
>
> > > > > * * * * * * * *If FoundCell Is Nothing Then
> > > > > * * * * * * * * * *Exit Do
> > > > > * * * * * * * *Else
> > > > > * * * * * * * * * *FoundCell.EntireRow.Delete
> > > > > * * * * * * * *End If
> > > > > * * * * * *End With
> > > > > * * * *Loop
> > > > > * *Next iCtr
> > > > > End Sub
>
> > Try this...
>
> > * * Dim MyRng As Range
> > * * Dim FoundCell As Range
> > * * Dim wks As Worksheet
> > * * Dim myStrings As Variant
> > * * Dim iCtr As Long
>
> > * * myStrings = Array("QMS Log Page") 'add more strings if you need
>
> > * * Set wks = ActiveSheet
>
> > * * With wks
> > * * * * Set MyRng = .Range("a2:a" & .Rows.Count)
> > * * End With
>
> > * * For iCtr = LBound(myStrings) To UBound(myStrings)
> > * * * * Do
> > * * * * * * With MyRng
> > * * * * * * * * Set FoundCell = .Cells.Find what:= "*" &
> > myStrings(iCtr) & "*" ' this will find anything that contains your
> > sting
> > * * * * * * * * If FoundCell Is Nothing Then
> > * * * * * * * * * * Exit Do
> > * * * * * * * * Else
> > * * * * * * * * * * FoundCell.EntireRow.Delete
> > * * * * * * * * End If
> > * * * * * * End With
> > * * * * Loop
> > * * Next iCtr
> > End Sub
>
> > Looking at your original code there were two lines that jumped out...
>
> > lookat:=xlWhole - You said your string starts with a date then QMS
> > etc. this would only find those cells that match exactly "QMS Log
> > Page"
> > after:=.Cells(.Cells.Count) - I am not sure what the point of this is?
>
> > James
>
> Thinking about it would it not be easier just to use the autofilter?
>
> * * Dim MyRng As Range
> * * Dim FoundCell As Range
> * * Dim wks As Worksheet
> * * Dim myStrings As Variant
> * * Dim iCtr As Long
>
> * * myStrings = Array("QMS Log Page") 'add more strings if you need
>
> * * Set wks = ActiveSheet
>
> * * With wks
> * * * * Set MyRng = .Range("a2:a" & .Rows.Count)
> * * * * set MyDeleteRng = .Range("a3:a" & .rows.count) ' This assumes
> header information in row 2?
> * * End With
>
> * * For iCtr = LBound(myStrings) To UBound(myStrings)
> * * * * MyRng.Autofilter 1, "*" myStrings(iCtr) & "*"
> * * * * MyDeleteRng.entirerow.delete
> * * Next iCtr
> * * myRng.AutofIlter ' switch the autofilter off
> End Sub
>
> James

This line:

MyRng.Autofilter 1, "*" myStrings(iCtr) & "*"

should be:

MyRng.Autofilter 1, "*" & myStrings(iCtr) & "*"

James

try this

Although not tested.

Sub Remove_Foreign()

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With

With Sheets("yoursheet")
.Select
Firstrow = yourfirstrow
Lastrow = yourlastrow

For Lrow = Lastrow To Firstrow Step -1

With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If .Value = "*"&"QMS"&"*" Then .EntireRow.ClearContents

End If

End With

Next Lrow

End With

With Application
CalcMode = .Calculation
.Calculation = xlAutomatic
End With

End Sub

HTH
Mark.