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