I'm trying to use a search box in excel vba with a wildcard. My sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value < JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager I,
Branch Manager II and so on. I would like enter: 'Branch' (without
the quote marks of course) and have all those job titles that start
with Branch remain while the other rows get deleted. As is, all rows
get deleted. Any ideas?

One way:

If Cells(i, 12).Text Like JobTitle & "*" Then


In article . com,
wrote:

I'm trying to use a search box in excel vba with a wildcard. My sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value < JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager I,
Branch Manager II and so on. I would like enter: 'Branch' (without
the quote marks of course) and have all those job titles that start
with Branch remain while the other rows get deleted. As is, all rows
get deleted. Any ideas?

On May 30, 11:13 am, JE McGimpsey wrote:
One way:

If Cells(i, 12).Text Like JobTitle & "*" Then

In article . com,



wrote:
I'm trying to use a search box in excel vba with a wildcard. My sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value < JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager I,
Branch Manager II and so on. I would like enter: 'Branch' (without
the quote marks of course) and have all those job titles that start
with Branch remain while the other rows get deleted. As is, all rows
get deleted. Any ideas?- Hide quoted text -

- Show quoted text -

Thanks for the quick repsone JE, however your suggestion deletes the
rows I want to keep. I want to do the reverse. I've tried modifing
what you suggested to:

if cells(i,12).text LIKE NOT JobTitle & "*" Then....but the error
message I get is Type MisMatch...any ideas?

On May 30, 11:33 am, wrote:
On May 30, 11:13 am, JE McGimpsey wrote:





One way:

If Cells(i, 12).Text Like JobTitle & "*" Then

In article . com,

wrote:
I'm trying to use a search box in excel vba with a wildcard. My sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value < JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager I,
Branch Manager II and so on. I would like enter: 'Branch' (without
the quote marks of course) and have all those job titles that start
with Branch remain while the other rows get deleted. As is, all rows
get deleted. Any ideas?- Hide quoted text -

- Show quoted text -

Thanks for the quick repsone JE, however your suggestion deletes the
rows I want to keep. I want to do the reverse. I've tried modifing
what you suggested to:

if cells(i,12).text LIKE NOT JobTitle & "*" Then....but the error
message I get is Type MisMatch...any ideas?- Hide quoted text -

- Show quoted text -

Thanks to all...Jeol, I tried your suggestion and that seems to
work ...thanks again....

I guess you can count on your users to enter the Job title with the exact
capitalization. If not, you might want to account for that.

? Left("Branch Manager",len("branch")) = "branch"
False
? Left("Branch Manager",len("Branch")) = "Branch"
True



--
Regards,
Tom Ogilvy


" wrote:

On May 30, 11:33 am, wrote:
On May 30, 11:13 am, JE McGimpsey wrote:





One way:

If Cells(i, 12).Text Like JobTitle & "*" Then

In article . com,

wrote:
I'm trying to use a search box in excel vba with a wildcard. My sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value < JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager I,
Branch Manager II and so on. I would like enter: 'Branch' (without
the quote marks of course) and have all those job titles that start
with Branch remain while the other rows get deleted. As is, all rows
get deleted. Any ideas?- Hide quoted text -

- Show quoted text -

Thanks for the quick repsone JE, however your suggestion deletes the
rows I want to keep. I want to do the reverse. I've tried modifing
what you suggested to:

if cells(i,12).text LIKE NOT JobTitle & "*" Then....but the error
message I get is Type MisMatch...any ideas?- Hide quoted text -

- Show quoted text -

Thanks to all...Jeol, I tried your suggestion and that seems to
work ...thanks again....

Could head a module dedicated for such string comparisons -

Option Compare Text

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
I guess you can count on your users to enter the Job title with the exact
capitalization. If not, you might want to account for that.

? Left("Branch Manager",len("branch")) = "branch"
False
? Left("Branch Manager",len("Branch")) = "Branch"
True



--
Regards,
Tom Ogilvy


" wrote:

On May 30, 11:33 am, wrote:
On May 30, 11:13 am, JE McGimpsey wrote:





One way:

If Cells(i, 12).Text Like JobTitle & "*" Then

In article . com,

wrote:
I'm trying to use a search box in excel vba with a wildcard. My
sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel
Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will
be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value < JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager
I,
Branch Manager II and so on. I would like enter: 'Branch'
(without
the quote marks of course) and have all those job titles that
start
with Branch remain while the other rows get deleted. As is, all
rows
get deleted. Any ideas?- Hide quoted text -

- Show quoted text -

Thanks for the quick repsone JE, however your suggestion deletes the
rows I want to keep. I want to do the reverse. I've tried modifing
what you suggested to:

if cells(i,12).text LIKE NOT JobTitle & "*" Then....but the error
message I get is Type MisMatch...any ideas?- Hide quoted text -

- Show quoted text -

Thanks to all...Jeol, I tried your suggestion and that seems to
work ...thanks again....

Try:

If Not Cells(i, 12) Like JobTitle & "*" Then

In article . com,
wrote:

Thanks for the quick repsone JE, however your suggestion deletes the
rows I want to keep. I want to do the reverse. I've tried modifing
what you suggested to:

if cells(i,12).text LIKE NOT JobTitle & "*" Then....but the error
message I get is Type MisMatch...any ideas?

try changing this line
from
If Cells(i, 12).Value < JobTitle & "*" then
to
If left(Cells(i, 12).Value,len(JobTitle) < JobTitle Then

" wrote:

I'm trying to use a search box in excel vba with a wildcard. My sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value < JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager I,
Branch Manager II and so on. I would like enter: 'Branch' (without
the quote marks of course) and have all those job titles that start
with Branch remain while the other rows get deleted. As is, all rows
get deleted. Any ideas?

Modified to delete everything except LIKE Branch....

Mike

Sub DeleteRows()
Dim JobTitle, FinDate As Date
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,proceed
with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = ActiveSheet.Range("L65536").End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value Like JobTitle & "*" Then

Else
Rows(i).Delete
End If

Next i
End Sub

" wrote:

I'm trying to use a search box in excel vba with a wildcard. My sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value < JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager I,
Branch Manager II and so on. I would like enter: 'Branch' (without
the quote marks of course) and have all those job titles that start
with Branch remain while the other rows get deleted. As is, all rows
get deleted. Any ideas?