input string as filter criteria in Excel

[mguy27]

So I have a list of data that is over 1 million rows long. the headers of the 5 colums are as follow:

Local Date Local Time Host Date Host Time Journal Message


What I need to do is cut down the size of the data to just what I need to look at. And I'm building this for a supervisor who is not very computer savvy. So, he'll click a button and the macro will take care of everything.
So he clicks the button, an InputBox pops up and he puts the criteria in there, which is going to be a single word. I then run a filter to show only the rows whose Journal Messages contain that inputed word. But I'm having problem using the input string in a 'containing' field.

For example, if I want the criteria to equal my data from the InputBox, I use:

myCrit = InputBox("Search Criteria")
ActiveSheet.Range("$A$1:$E$230").AutoFilter Field:=5, Criteria1:=myCrit

If I want it to filter for strings containing a single word, I use this:

ActiveSheet.Range("$A$1:$E$230").AutoFilter Field:=5, Criteria1:= "=*word*"

So I need someway to replace 'word' with myCrit. Any ideas? Here is my full code:

Sub filt2()
'
' filt2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Dim myCrit As String
    
    myCrit = InputBox("Search Criteria")
    
     Range("P3").Select
    ActiveCell.Offset(1, 0).FormulaR1C1 = myCrit
    
    Columns("A:E").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.AutoFilter
    Sheets("Sheet1").Select
    Range("P4").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Range("$A$1:$E$230").AutoFilter Field:=5, Criteria1:= _
        "=*word*", Operator:=xlAnd
    Columns("A:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Sheets("Sheet1").Select
    Columns("E:E").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Number of Incidents:"
        Range("H1").Select
    ActiveCell.FormulaR1C1 = "=COUNT(R[1]C[-7]:R[1048575]C[-7])"
    Range("H2").Select
    Sheets("Sheet1").Name = "Selected Incidents"
    Sheets("Sheet2").Name = "Master List"
End Sub

Thanks, Taylor

PS - Just ingnore the useless pasting and copying and switching from sheet to sheet.

 

[Gavona]

Surely a million rows won't fit on the spreadsheet in the first place?

Criteria1:=("*" + myCrit + "*")


Gavin

 

[mguy27]

from what I've seen so far, it looks like the max is 1,048,576 rows....kind of a random number if you ask me.

Thanks for the help!!!

-Taylor

 

[strongm]

>the max is 1,048,576

AH, so you are using Excel 2007. Be warned that earlier versions of Excel only support 65536 rows.

>kind of a random number

Not really. You have to remember that computers are at heart working in binary. And the row limitation in both Excel 2007 and the limitation in earlier versions seem less random if expressed in binary or hexadecimal:

65536 = 10000 Hex
1048576 = 100000 Hex

 

[Gavona]

If number of rows is an issue look at get external data / MSQuery to get the data you want into excell

Gavin

 

[SkipVought]

I deal with source tables with millions of rows. I most often extract data from these tables, in Excel using MS Query. Query grid works very similar to Access.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}