Click a button then user is asked what qty to filter or hide rows based on user input

I haven't yet figured out how to enter an array, but I'm working on it

If we can figure this out using a recorded macro that is fine with me.

You'll probably want to play with this button:

Sub Macro20()
'
' Macro20 Macro
'

'
    ActiveSheet.Buttons.Add(26.25, 5.25, 72, 72).Select
    Selection.OnAction = "HQH"
    End Sub

And, I had something grander in mind but this seems to work!

Sub HQH(): Dim S As String, U As Range, n As Long, r As Long, Z
Rows.Hidden = False: Cells(1, 2).ClearComments

'Enter numbers with a comma between each one
S = InputBox("What quantities would you like to be Hidden?")
If S = "" Then Exit Sub

Z = Split(S, ",")

For r = 1 To Rows.Find("*", , , , xlByRows, xlPrevious).Row
For n = LBound(Z) To UBound(Z)
If Cells(r, 1) = Val(Z(n)) Then
If U Is Nothing Then Set U = Cells(r, 1) Else Set U = Union(U, Cells(r, 1))
Exit For
End If
Next n
Next r

If Not U Is Nothing Then U.EntireRow.Hidden = True
S = "Rows with quantities " & S & " are now Hidden"
Cells(1, 2).AddComment (S)

End Sub

Just run the recorded macro to place the button (I wasn't very fussy)

The HQH routine goes in a standard module:

Directions for running the routine(s) just supplied

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name (just push the button)


*Save the book as macro-enabled

Did you only want to be able to enter one number? The way it is now, there's no limit.

You reset it by entering a null - just hit the enter key.

*Each entry separated by a comma

One number would be ideal. I did try 1,10 and it hid 1 and 10. I tried 1-10 and hid the same 2. The best would be enter 10 and 10 and below get hidden. A range be second best, say enter 1-10 which would hide 1-10.

The null entry worked nicely and I liked how you added the little comment to tell the user what rows are hidden.

Thanks for everything.

Regards,

-j

This is your preference:

Sub HQHX(): Dim S As String, U As Range, n As Long, r As Long
Rows.Hidden = False: Cells(1, 2).ClearComments

'Enter the highest number that you want to hide
S = InputBox("What is your upper limit?")
If S = "" Then Exit Sub
                n = Val(S)
For r = 2 To Rows.Find("*", , , , xlByRows, xlPrevious).Row

If Cells(r, 1) <= n Then
If U Is Nothing Then Set U = Cells(r, 1) Else Set U = Union(U, Cells(r, 1))
End If

Next r

If Not U Is Nothing Then U.EntireRow.Hidden = True
S = "Rows with quantities <= " & S & " are now Hidden"
Cells(1, 2).AddComment (S)

End Sub

Just change:

Cells(r, 1)

to

Cells(r, 6)

And - thanks for the rep!

Everywhere it appears. By all means, if your data starts in row 6 the the loop should start at row 6 - or whatever