This code assume that your data start in A1 and that this is the header cell of the first column
I filter column 1 in this example (A)
'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str
If your data start in A and you want to filter in F
Use this then (column 6 = F)
rng.AutoFilter Field:=6, Criteria1:=Str
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Rumish8086" wrote in message ...
My only problem is (please bare with my here):
The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)
Yet when I replace "A1" with "F1," this too does not work.
What's happening?
Thanks.
"Ron de Bruin" wrote:
Install EasyFilter if you want it to be easy
But with code you can do this
http://www.rondebruin.nl/copy5.htm#one
I add a inputbox in the code example below
Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String
Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub
Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change
'Close AutoFilter first
WS.AutoFilterMode = False
'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str
Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False
On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...
...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.
Do you see what I mean?
Thanks.
"Ron de Bruin" wrote:
Hi
If the value is in one column ? you can use DataAutoFilter to do this
See
http://www.contextures.com/xlautofilter01.html
I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.
Thank you again.
===
My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.
Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.
Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.
Easy as that, but I don't know where to begin. Can someone help?
Thanks!