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