VBA to Hide Rows with user defined criteria

[leebrockney]

I am trying to write a quick VBA macro to hide rows where a specific cell value does not meet a required value. I am easily able to do this using the following code.

Sub OSR_ReportComplete()
Dim cell As Range
For Each cell In Range("DataRange")
If UCase(cell.Value) < 50000 Then
cell.EntireRow.Hidden = True
End If
Next
End Sub

The issue is that this makes me hard code in the criteria (50000). I want to have the person running the macro enter the criteria. I have a cell named "Limit" that the user enters their custom setting. when I reference the cell name like below. it does not work. what am I doing wrong?


Sub OSR_ReportComplete()
Dim cell As Range
For Each cell In Range("DataRange")
If UCase(cell.Value) < Range("Limit").Value Then
cell.EntireRow.Hidden = True
End If
Next
End Sub


Thanks!!

Lee

 

[Joe4]

Why not just use Excel's built-in Filters?
There is a number filter option for "Less than" and then you can choose any number you want.
You can pre-set up the Filters, so all they need to do is click on the header at the top of the row and select their desired option and value.

 

[leebrockney]

Why not just use Excel's built-in Filters?
There is a number filter option for "Less than" and then you can choose any number you want.
You can pre-set up the Filters, so all they need to do is click on the header at the top of the row and select their desired option and value.

Thanks for the reply. I unfortunately cannot use filters as this is not a standard excel doc. it is an excel add-in tool that displays real-time GL data each time the document is opened. when it is opened I have the ability to kick off a macro before displaying the results to the user. hence the macro that hides the data the user does not need to see based on the parameter value they enter for "Limit"

 

[leebrockney]

Is there no way to do this then? thought this would be an easy question to answer......

 

[Joe4]

Why exactly are you using "Ucase"?
"Ucase" is a text function that says convert a text entry to upper case (i.e. convert "dog" to "DOG").
Why would you be using this on numbers?
Using CASE which change it from number to text, and can mess up your inequality check. Get rid of it.

 

[HackSlash]

I'm not sure if what Joe said fixed your problem. I replaced your UCASE with "Val" which will convert the string to a double before checking if it is less than the limit. You might also want to ensure that your limit is a number too.

Try this:

Sub OSR_ReportComplete()
    Dim cell As Range
    For Each cell In Range("DataRange")
        If Val(cell.Value) < [Limit] Then
            cell.EntireRow.Hidden = True
        End If
    Next
End Sub