I desperatly need a macro that will;

1. Ask me to input a column letter
2. Ask me to input a search string
3. Ask me to input a output file name
3. Search the entire column and delete all rows in which the search string
is NOT found.
4. Save the results to the output file name

*** The column rows will all have some other information than the search
string *** such as "Earle Ike Toyota Volvo"

I don't know if you need the following but here it is.
I use excel 2002 - the worksheet contains no more than 15 columns but can
have up to 25,000 rows. I work with automobile dealers and I have a
worksheet with all dealership names, address and franchise names. I need to
go into the info, delete all names that (lets say) do NOT have Toyota (search
string) in them and then save the results to a new worksheet named Toyota but
without changing (saving) the origional file. I can do the saving manually
but it would be nice to have that in the macro also.
I hope i covered it well enough to answer any questions. If not, my email is

Thanks in advance for any help given. I really appreciate it.
Jim

Try this one. To get the macro into your workbook, open the workbook, press
[Alt]+[F11] to enter the VB Editor. Use Insert | Module to create a new code
module in the VBE and copy the code below and paste it into the blank code
module presented to you. Close the VBE. Use Tools | Macro | Macros to use
the macro when you have the sheet you need to 'trim down' selected.

Notice that leaving the entry blank when the info items (search text, search
column or new filename) will abort the process.

Sub CreateNewWorkbook()
'this is set up to assume Row 1 contains labels
Const firstDataRow = 2
Dim searchString As String
Dim searchColumn As String
Dim newFileName As String
Dim RLC As Long ' Row Loop Counter

searchString = InputBox("Enter text to find:", "Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column
Entry", ""))
If searchColumn = "" Then
Exit Sub
End If
newFileName = Trim(InputBox("Enter Name for the new file:", "New File
Name", ""))
If newFileName = "" Or _
Len(newFileName) < 5 Then
Exit Sub
End If
If UCase(Right(newFileName, 4)) < ".XLS" Then
newFileName = newFileName & ".xls"
End If
'loop works from the bottom up
'this will make the comparison regardless of upper/lower case spellings
'i.e. toyota = Toyota = TOYota = TOYOTA, etc.
searchString = UCase(searchString)

For RLC = Range(searchColumn & Rows.Count).End(xlUp).Row To _
firstDataRow Step -1
If InStr(UCase(Range(searchColumn & RLC)), searchString) = 0 Then
Range(searchColumn & RLC).EntireRow.Delete
End If
Next
'save the file here
ThisWorkbook.SaveAs newFileName

End Sub


"JayKay100" wrote:

I desperatly need a macro that will;

1. Ask me to input a column letter
2. Ask me to input a search string
3. Ask me to input a output file name
3. Search the entire column and delete all rows in which the search string
is NOT found.
4. Save the results to the output file name

*** The column rows will all have some other information than the search
string *** such as "Earle Ike Toyota Volvo"

I don't know if you need the following but here it is.
I use excel 2002 - the worksheet contains no more than 15 columns but can
have up to 25,000 rows. I work with automobile dealers and I have a
worksheet with all dealership names, address and franchise names. I need to
go into the info, delete all names that (lets say) do NOT have Toyota (search
string) in them and then save the results to a new worksheet named Toyota but
without changing (saving) the origional file. I can do the saving manually
but it would be nice to have that in the macro also.
I hope i covered it well enough to answer any questions. If not, my email is

Thanks in advance for any help given. I really appreciate it.
Jim

"JayKay100" wrote:

I desperatly need a macro that will;

1. Ask me to input a column letter
2. Ask me to input a search string
3. Ask me to input a output file name
3. Search the entire column and delete all rows in which the search string
is NOT found.
4. Save the results to the output file name

*** The column rows will all have some other information than the search
string *** such as "Earle Ike Toyota Volvo"

I don't know if you need the following but here it is.
I use excel 2002 - the worksheet contains no more than 15 columns but can
have up to 25,000 rows. I work with automobile dealers and I have a
worksheet with all dealership names, address and franchise names. I need to
go into the info, delete all names that (lets say) do NOT have Toyota (search
string) in them and then save the results to a new worksheet named Toyota but
without changing (saving) the origional file. I can do the saving manually
but it would be nice to have that in the macro also.
I hope i covered it well enough to answer any questions. If not, my email is

Thanks in advance for any help given. I really appreciate it.
Jim

Evenf with my dismal lack of macro "Smarts" I can tell that we are on the
right track but the macro seems to gag on these entries. (It does not get to
the point where it asks me for any input......)

searchString = InputBox("Enter text to find:", "Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column
Entry ", ""))"
If searchColumn = "" Then
Exit Sub

Should not the searchString and searchColumn entries look almost identical?
I notice one says trim(input box( and the other does not and some other small
differences. Maybe it doesnt make any difference but I am afraid to make any
changes.

Jim

Make certain that Macro Security (Tools | macro | Security in 2003 and
earlier) is set to Medium. It may be set on High or Very High which would
keep the macro from running and not offer you the opportunity to [Enable]
macros.

As for the Trim function - Trim() removes leading and trailing white-space
from a text entry. So we remove it from the input you provide for a column
ID letter (or letters) since column letters don't have spaces before/after
them. And that entry (in which I misspelled column as columnu) is looking
for an entry like "A" or "Z" or "AB", not a title in a column row.

I don't remove any white-space from the search text because you may want to
enter something like (without " marks) " toyota " to catch only entries where
toyota is a whole word and not part of something like " ToyotasAreUs".

You say it's gagging - is it throwing up any error messages or just not
running at all? since the first executable line is the 'searchString =
InputBox("...' line of code either you should get an error there, or you
should get a kind of message box with an input area for you to type into. If
you're not getting that, then I suspect macro execution is turned off, and
the suggestion in my first paragraph should help.

NOTE: once you change the Macro Security level, you have to close and then
reopen Excel for the changes to take effect.

Hope this helps.

"JayKay100" wrote:

Evenf with my dismal lack of macro "Smarts" I can tell that we are on the
right track but the macro seems to gag on these entries. (It does not get to
the point where it asks me for any input......)

searchString = InputBox("Enter text to find:", "Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column
Entry ", ""))"
If searchColumn = "" Then
Exit Sub

Should not the searchString and searchColumn entries look almost identical?
I notice one says trim(input box( and the other does not and some other small
differences. Maybe it doesnt make any difference but I am afraid to make any
changes.

Jim

When I try to run the macro it immediately takes me back in the VBA with a
box that says
€œCompile error
Syntax Error€�

Then where it says "Sub CreateNewWorkbook()" is in yellow and where it says
"searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column"
is in red.

Also this is in red "newFileName = Trim(InputBox("Enter Name for the new
file:", "New File"
Name", ""))

I hope this helps€¦. I also hope I am not doing something wrong€¦. Lol

My security level is set to €œLOW€� - I have tried running on two machines,
one is xp and one is vista.... results are the same

Also, I have ran other macros on both machines in the past so I don't think
it is a machine problem but, of course, I am really not qualified to make
that decision.


Best Regards,
Jim


"JLatham" wrote:

Make certain that Macro Security (Tools | macro | Security in 2003 and
earlier) is set to Medium. It may be set on High or Very High which would
keep the macro from running and not offer you the opportunity to [Enable]
macros.

As for the Trim function - Trim() removes leading and trailing white-space
from a text entry. So we remove it from the input you provide for a column
ID letter (or letters) since column letters don't have spaces before/after
them. And that entry (in which I misspelled column as columnu) is looking
for an entry like "A" or "Z" or "AB", not a title in a column row.

I don't remove any white-space from the search text because you may want to
enter something like (without " marks) " toyota " to catch only entries where
toyota is a whole word and not part of something like " ToyotasAreUs".

You say it's gagging - is it throwing up any error messages or just not
running at all? since the first executable line is the 'searchString =
InputBox("...' line of code either you should get an error there, or you
should get a kind of message box with an input area for you to type into. If
you're not getting that, then I suspect macro execution is turned off, and
the suggestion in my first paragraph should help.

NOTE: once you change the Macro Security level, you have to close and then
reopen Excel for the changes to take effect.

Hope this helps.

"JayKay100" wrote:

Evenf with my dismal lack of macro "Smarts" I can tell that we are on the
right track but the macro seems to gag on these entries. (It does not get to
the point where it asks me for any input......)

searchString = InputBox("Enter text to find:", "Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column
Entry ", ""))"
If searchColumn = "" Then
Exit Sub

Should not the searchString and searchColumn entries look almost identical?
I notice one says trim(input box( and the other does not and some other small
differences. Maybe it doesnt make any difference but I am afraid to make any
changes.

Jim

Just for the heck of it I wrote the following macro. It works fine..... I
just wanted to make sure it wasn't a machine or software problem....

Sub Test()
searchString = InputBox("Enter text to find:", "Search Text Entry", "")
End Sub

First macro I ever wrote lol

Jim


"JLatham" wrote:

Make certain that Macro Security (Tools | macro | Security in 2003 and
earlier) is set to Medium. It may be set on High or Very High which would
keep the macro from running and not offer you the opportunity to [Enable]
macros.

As for the Trim function - Trim() removes leading and trailing white-space
from a text entry. So we remove it from the input you provide for a column
ID letter (or letters) since column letters don't have spaces before/after
them. And that entry (in which I misspelled column as columnu) is looking
for an entry like "A" or "Z" or "AB", not a title in a column row.

I don't remove any white-space from the search text because you may want to
enter something like (without " marks) " toyota " to catch only entries where
toyota is a whole word and not part of something like " ToyotasAreUs".

You say it's gagging - is it throwing up any error messages or just not
running at all? since the first executable line is the 'searchString =
InputBox("...' line of code either you should get an error there, or you
should get a kind of message box with an input area for you to type into. If
you're not getting that, then I suspect macro execution is turned off, and
the suggestion in my first paragraph should help.

NOTE: once you change the Macro Security level, you have to close and then
reopen Excel for the changes to take effect.

Hope this helps.

"JayKay100" wrote:

Evenf with my dismal lack of macro "Smarts" I can tell that we are on the
right track but the macro seems to gag on these entries. (It does not get to
the point where it asks me for any input......)

searchString = InputBox("Enter text to find:", "Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column
Entry ", ""))"
If searchColumn = "" Then
Exit Sub

Should not the searchString and searchColumn entries look almost identical?
I notice one says trim(input box( and the other does not and some other small
differences. Maybe it doesnt make any difference but I am afraid to make any
changes.

Jim

Ok, The problem is the forum here - it breaks long lines and if those lines
happen to be code and you copy them from here, then they don't work in a code
module. I didn't keep the lines short enough, or put enough breaks in them
that the VB Editor would understand.

Here's a revised version of the code that the editor here shouldn't break up
and you should be able to copy and paste (over the old code) without
problems. The space followed by an underscore at the end of some lines tells
the VB Editor that the "logical" line continues on to the next physical line.


BTW: EXCELLENT!! Job of writing your own macro and beginning the debugging
process. Really.

Sub CreateNewWorkbook()
'this is set up to assume Row 1 contains labels
Const firstDataRow = 2
Dim searchString As String
Dim searchColumn As String
Dim newFileName As String
Dim RLC As Long ' Row Loop Counter

searchString = InputBox("Enter text to find:", _
"Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter column to search in:", _
"Search Column Entry ", ""))
If searchColumn = "" Then
Exit Sub
End If
newFileName = Trim(InputBox("Enter Name for the new file:", _
"New File Name", ""))
If newFileName = "" Or _
Len(newFileName) < 5 Then
Exit Sub
End If
If UCase(Right(newFileName, 4)) < ".XLS" Then
newFileName = newFileName & ".xls"
End If
'loop works from the bottom up
'this will make the comparison regardless of
'upper/lower case spellings
'i.e. toyota = Toyota = TOYota = TOYOTA, etc.
searchString = UCase(searchString)

For RLC = Range(searchColumn & _
Rows.Count).End(xlUp).Row To _
firstDataRow Step -1
If InStr(UCase(Range(searchColumn & RLC)), _
searchString) = 0 Then
Range(searchColumn & RLC).EntireRow.Delete
End If
Next
'save the file here
ThisWorkbook.SaveAs newFileName
End Sub


"JayKay100" wrote:

Just for the heck of it I wrote the following macro. It works fine..... I
just wanted to make sure it wasn't a machine or software problem....

Sub Test()
searchString = InputBox("Enter text to find:", "Search Text Entry", "")
End Sub

First macro I ever wrote lol

Jim


"JLatham" wrote:

Make certain that Macro Security (Tools | macro | Security in 2003 and
earlier) is set to Medium. It may be set on High or Very High which would
keep the macro from running and not offer you the opportunity to [Enable]
macros.

As for the Trim function - Trim() removes leading and trailing white-space
from a text entry. So we remove it from the input you provide for a column
ID letter (or letters) since column letters don't have spaces before/after
them. And that entry (in which I misspelled column as columnu) is looking
for an entry like "A" or "Z" or "AB", not a title in a column row.

I don't remove any white-space from the search text because you may want to
enter something like (without " marks) " toyota " to catch only entries where
toyota is a whole word and not part of something like " ToyotasAreUs".

You say it's gagging - is it throwing up any error messages or just not
running at all? since the first executable line is the 'searchString =
InputBox("...' line of code either you should get an error there, or you
should get a kind of message box with an input area for you to type into. If
you're not getting that, then I suspect macro execution is turned off, and
the suggestion in my first paragraph should help.

NOTE: once you change the Macro Security level, you have to close and then
reopen Excel for the changes to take effect.

Hope this helps.

"JayKay100" wrote:

Evenf with my dismal lack of macro "Smarts" I can tell that we are on the
right track but the macro seems to gag on these entries. (It does not get to
the point where it asks me for any input......)

searchString = InputBox("Enter text to find:", "Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column
Entry ", ""))"
If searchColumn = "" Then
Exit Sub

Should not the searchString and searchColumn entries look almost identical?
I notice one says trim(input box( and the other does not and some other small
differences. Maybe it doesnt make any difference but I am afraid to make any
changes.

Jim

That did it! Works perfectly, just what I wanted!

Thanks so much - I know you spent a lot of time on this but I really, really
appreciate it!

I wished you lived next door so, in situations like this, I could get
instant gratification on my excell problems!

Best Regards and Happy Holidays!
Jim K
San Dimas, CA

"JLatham" wrote:

Ok, The problem is the forum here - it breaks long lines and if those lines
happen to be code and you copy them from here, then they don't work in a code
module. I didn't keep the lines short enough, or put enough breaks in them
that the VB Editor would understand.

Here's a revised version of the code that the editor here shouldn't break up
and you should be able to copy and paste (over the old code) without
problems. The space followed by an underscore at the end of some lines tells
the VB Editor that the "logical" line continues on to the next physical line.


BTW: EXCELLENT!! Job of writing your own macro and beginning the debugging
process. Really.

Sub CreateNewWorkbook()
'this is set up to assume Row 1 contains labels
Const firstDataRow = 2
Dim searchString As String
Dim searchColumn As String
Dim newFileName As String
Dim RLC As Long ' Row Loop Counter

searchString = InputBox("Enter text to find:", _
"Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter column to search in:", _
"Search Column Entry ", ""))
If searchColumn = "" Then
Exit Sub
End If
newFileName = Trim(InputBox("Enter Name for the new file:", _
"New File Name", ""))
If newFileName = "" Or _
Len(newFileName) < 5 Then
Exit Sub
End If
If UCase(Right(newFileName, 4)) < ".XLS" Then
newFileName = newFileName & ".xls"
End If
'loop works from the bottom up
'this will make the comparison regardless of
'upper/lower case spellings
'i.e. toyota = Toyota = TOYota = TOYOTA, etc.
searchString = UCase(searchString)

For RLC = Range(searchColumn & _
Rows.Count).End(xlUp).Row To _
firstDataRow Step -1
If InStr(UCase(Range(searchColumn & RLC)), _
searchString) = 0 Then
Range(searchColumn & RLC).EntireRow.Delete
End If
Next
'save the file here
ThisWorkbook.SaveAs newFileName
End Sub


"JayKay100" wrote:

Just for the heck of it I wrote the following macro. It works fine..... I
just wanted to make sure it wasn't a machine or software problem....

Sub Test()
searchString = InputBox("Enter text to find:", "Search Text Entry", "")
End Sub

First macro I ever wrote lol

Jim


"JLatham" wrote:

Make certain that Macro Security (Tools | macro | Security in 2003 and
earlier) is set to Medium. It may be set on High or Very High which would
keep the macro from running and not offer you the opportunity to [Enable]
macros.

As for the Trim function - Trim() removes leading and trailing white-space
from a text entry. So we remove it from the input you provide for a column
ID letter (or letters) since column letters don't have spaces before/after
them. And that entry (in which I misspelled column as columnu) is looking
for an entry like "A" or "Z" or "AB", not a title in a column row.

I don't remove any white-space from the search text because you may want to
enter something like (without " marks) " toyota " to catch only entries where
toyota is a whole word and not part of something like " ToyotasAreUs".

You say it's gagging - is it throwing up any error messages or just not
running at all? since the first executable line is the 'searchString =
InputBox("...' line of code either you should get an error there, or you
should get a kind of message box with an input area for you to type into. If
you're not getting that, then I suspect macro execution is turned off, and
the suggestion in my first paragraph should help.

NOTE: once you change the Macro Security level, you have to close and then
reopen Excel for the changes to take effect.

Hope this helps.

"JayKay100" wrote:

Evenf with my dismal lack of macro "Smarts" I can tell that we are on the
right track but the macro seems to gag on these entries. (It does not get to
the point where it asks me for any input......)

searchString = InputBox("Enter text to find:", "Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column
Entry ", ""))"
If searchColumn = "" Then
Exit Sub

Should not the searchString and searchColumn entries look almost identical?
I notice one says trim(input box( and the other does not and some other small
differences. Maybe it doesnt make any difference but I am afraid to make any
changes.

Jim

Glad we got it to work the 2nd time around.

As for me living next door - through these forums you live next door to many
excellent sources of Excel info and at least one of us is "home" almost all
the time.


"jaykay100" wrote:

That did it! Works perfectly, just what I wanted!

Thanks so much - I know you spent a lot of time on this but I really, really
appreciate it!

I wished you lived next door so, in situations like this, I could get
instant gratification on my excell problems!

Best Regards and Happy Holidays!
Jim K
San Dimas, CA

"JLatham" wrote:

Ok, The problem is the forum here - it breaks long lines and if those lines
happen to be code and you copy them from here, then they don't work in a code
module. I didn't keep the lines short enough, or put enough breaks in them
that the VB Editor would understand.

Here's a revised version of the code that the editor here shouldn't break up
and you should be able to copy and paste (over the old code) without
problems. The space followed by an underscore at the end of some lines tells
the VB Editor that the "logical" line continues on to the next physical line.


BTW: EXCELLENT!! Job of writing your own macro and beginning the debugging
process. Really.

Sub CreateNewWorkbook()
'this is set up to assume Row 1 contains labels
Const firstDataRow = 2
Dim searchString As String
Dim searchColumn As String
Dim newFileName As String
Dim RLC As Long ' Row Loop Counter

searchString = InputBox("Enter text to find:", _
"Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter column to search in:", _
"Search Column Entry ", ""))
If searchColumn = "" Then
Exit Sub
End If
newFileName = Trim(InputBox("Enter Name for the new file:", _
"New File Name", ""))
If newFileName = "" Or _
Len(newFileName) < 5 Then
Exit Sub
End If
If UCase(Right(newFileName, 4)) < ".XLS" Then
newFileName = newFileName & ".xls"
End If
'loop works from the bottom up
'this will make the comparison regardless of
'upper/lower case spellings
'i.e. toyota = Toyota = TOYota = TOYOTA, etc.
searchString = UCase(searchString)

For RLC = Range(searchColumn & _
Rows.Count).End(xlUp).Row To _
firstDataRow Step -1
If InStr(UCase(Range(searchColumn & RLC)), _
searchString) = 0 Then
Range(searchColumn & RLC).EntireRow.Delete
End If
Next
'save the file here
ThisWorkbook.SaveAs newFileName
End Sub


"JayKay100" wrote:

Just for the heck of it I wrote the following macro. It works fine..... I
just wanted to make sure it wasn't a machine or software problem....

Sub Test()
searchString = InputBox("Enter text to find:", "Search Text Entry", "")
End Sub

First macro I ever wrote lol

Jim


"JLatham" wrote:

Make certain that Macro Security (Tools | macro | Security in 2003 and
earlier) is set to Medium. It may be set on High or Very High which would
keep the macro from running and not offer you the opportunity to [Enable]
macros.

As for the Trim function - Trim() removes leading and trailing white-space
from a text entry. So we remove it from the input you provide for a column
ID letter (or letters) since column letters don't have spaces before/after
them. And that entry (in which I misspelled column as columnu) is looking
for an entry like "A" or "Z" or "AB", not a title in a column row.

I don't remove any white-space from the search text because you may want to
enter something like (without " marks) " toyota " to catch only entries where
toyota is a whole word and not part of something like " ToyotasAreUs".

You say it's gagging - is it throwing up any error messages or just not
running at all? since the first executable line is the 'searchString =
InputBox("...' line of code either you should get an error there, or you
should get a kind of message box with an input area for you to type into. If
you're not getting that, then I suspect macro execution is turned off, and
the suggestion in my first paragraph should help.

NOTE: once you change the Macro Security level, you have to close and then
reopen Excel for the changes to take effect.

Hope this helps.

"JayKay100" wrote:

Evenf with my dismal lack of macro "Smarts" I can tell that we are on the
right track but the macro seems to gag on these entries. (It does not get to
the point where it asks me for any input......)

searchString = InputBox("Enter text to find:", "Search Text Entry", "")
If Trim(searchString) = "" Then
Exit Sub
End If
searchColumn = Trim(InputBox("Enter columnu to search in:", "Search Column
Entry ", ""))"
If searchColumn = "" Then
Exit Sub

Should not the searchString and searchColumn entries look almost identical?
I notice one says trim(input box( and the other does not and some other small
differences. Maybe it doesnt make any difference but I am afraid to make any
changes.

Jim