Input box to filter rows

[kathleen0927]

Hey! Looking for some help with creating an input box to filter an Excel spreadsheet that has about 1900 rows of data.

I want to have a push button control that will produce an input box where the end user will enter data to filter the list. Data will be located in column A starting in row 11. When the user enters info ("ABC1"), the list will be filtered showing only rows with that code in column A. I'd also like the selection entered ("ABC1") to be inserted in my header in cell C4 for reference.

I have never worked with input boxes before so everything I am looking at, is failing miserably!

 

[dmt32]

Hi

try following & see if does what you want


Place code in a STANDARD module

Sub FilterData()
    Dim FilterRange As Range, Rng As Range
    Dim GetInput As Variant
    
    Do
        GetInput = InputBox("Enter Search Criteria", "Search")
'cancel pressed
        If StrPtr(GetInput) = 0 Then Exit Sub
    Loop Until Len(GetInput) > 0
    
    With ThisWorkbook.Worksheets("Sheet1")
'set filter range (sheet must not be protected)
    Set FilterRange = .Range("A11").CurrentRegion
'insert to header in cell C4 for reference.
        .Range("C4").Value = GetInput
    End With
    
    With FilterRange
'clear filter
        .Cells(1, 1).AutoFilter
        .AutoFilter Field:=1, Criteria1:=GetInput, VisibleDropDown:=True
    End With
    
    Set Rng = FilterRange.Parent.AutoFilter.Range
    
'check for matching criteria
    If Rng.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
'clear filter
       Rng.AutoFilter
'inform user
        MsgBox GetInput & Chr(10) & "Search Value Not Found", 48, "Not Found"
        
    End If
    
End Sub

Change sheet name shown in RED as required

It is assumed that Row 11 is the HEADER row. Also, your worksheet is not protected

Hope helpful

Dave.

 

[kathleen0927]

Thank you for the quick reply, Dave.

I have a password on the file so I added "ActiveSheet.Unprotect Password:" and "ActiveSheet.Unprotect Password:" code before and after the "If" statements.

Row 11 is where my data starts, with rows 1 - 10 as the header. I changed the Filter range to "A10" and it works except that it also hides rows 9 and 10 of the header. Do you know how I would adjust this so all header rows (1 - 10) remain? Also is there a way to force the enter to CAPS (but still allow numbers) so when it inserts the input value in C4 it will appear like "ABC1" not "abc1"?

Thanks for your help with this!

 

[dmt32]

Hi,
seems like code going in right direction

see if this update helps

Sub FilterData()
    Dim FilterRange As Range, Rng As Range
    Dim GetInput As Variant
    Dim ws As Worksheet
    
'add sheet protect password if required
    Const wsPassword As String = ""
    
'change sheet name as required
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    
    Do
        GetInput = InputBox("Enter Search Criteria", "Search")
'cancel pressed
        If StrPtr(GetInput) = 0 Then Exit Sub
    Loop Until Len(GetInput) > 0
    
    With ws
'unprotect sheet
        .Unprotect Password:=wsPassword
'set filter range
    Set FilterRange = .Range("A1").CurrentRegion
'ignore header rows
    Set FilterRange = FilterRange.Offset(10, 0).Resize(FilterRange.Rows.Count - 10)
'insert to header in cell C4 for reference.
        .Range("C4").Value = UCase(GetInput)
    End With
    
    With FilterRange
'apply filter
        .Rows(1).AutoFilter
        .AutoFilter Field:=1, Criteria1:=GetInput, VisibleDropDown:=True
    End With
    
    Set Rng = FilterRange.Parent.AutoFilter.Range
    
'check for matching criteria
    If Rng.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
'clear filter
       Rng.AutoFilter
'inform user
        MsgBox GetInput & Chr(10) & "Search Value Not Found", 48, "Not Found"
        
    End If
    
    ws.Protect Password:=wsPassword
End Sub

Dave

 

[kathleen0927]

I got a run time error 1004 on the following line according to the debugger

Set FilterRange =FilterRange.Offset(10,0).Resize(FilterRange.Rows.Count -10)

 

[dmt32]

Interesting, worked ok on test file I just created

Did you use the code unaltered as posted or just update existing code?

Dave

 

[kathleen0927]

I copied/ pasted into a new module. I only added the password to the Const wsPassword As String = "" line. I have been trying to figure it out but i am stumped.

OK - As I was typing this, i looked at the file and realized that there was no data in column A for the header rows. I added dummy data and it worked! Holy Moley!

Thanks so much Dave for all your help. You went above and beyond for me!

 

[dmt32]

OK - As I was typing this, i looked at the file and realized that there was no data in column A for the header rows. I added dummy data and it worked! Holy Moley!

Thanks so much Dave for all your help. You went above and beyond for me!

There is always a reason & good you sorted it.

Glad I was of help & solution does what you want.

Dave