Using an Input Box To Define a Range

[WBTKbeezy]

I am trying to set a variable to a range by using an input box. Basically
it's a macro that will ask a user what range of cells the data they are
looking for is in, but I can't seem to get it. I tried it once and entered
C1:C120 into the input box but then I got an error that said Object variable
not set (and the debug highlight was on the input box for the range.

Any help?

 

[JLGWhiz]

It is easier when you post the code.

 

[WBTKbeezy]

Sub HIDEROWS()

Application.ScreenUpdating = False

Dim HideCell As Range
Dim HideRange As String
Dim HideValue As String

HideRange = InputBox("What is the Range of cells the Hide Values In?")
HideValue = InputBox("What is the value of the Hiding Range Text?")

For Each HideCell In HideRange.Rows

If HideCell = HideValue Then
HideCell.EntireRow.Hidden = True
End If

Next HideCell

End Sub

 

[Rick Rothstein \(MVP - VB\)]

The return from an InputBox is Text, not a Range. Try adding this subroutine
to your code window...

Sub SetRange(RangeVariable As Range, Prompt As String)
Dim Answer As String
On Error Resume Next
Do
Answer = InputBox(Prompt)
If Len(Answer) = 0 Then
Exit Do
Else
Set RangeVariable = Range(Answer)
End If
Loop While RangeVariable Is Nothing
End Sub

and then, as one example, set your ranges like this...

Dim HideValue As Range
SetRange HideValue, "What is the value of the Hiding Range Text?"
If Not HideValue Is Nothing Then
' Put your actual code here
Debug.Print HideValue.Address
End If

Note the need to test your range against Nothing... that is because I
allowed the user to exit the SetRange subroutine by clicking OK when the
input field is empty... I did this so the user wouldn't be trapped in an
endless loop if they decided not to input a range for some reason. If you
don't want to let the user escape having to put in an actual range, then you
can use this subroutine instead...

Sub SetRange(RangeVariable As Range, Prompt As String)
Dim Answer As String
On Error Resume Next
Do
Set RangeVariable = Range(InputBox(Prompt))
Loop While RangeVariable Is Nothing
End Sub

and you won't have to test the returned range against Nothing any more...

Dim HideValue As Range
SetRange HideValue, "What is the value of the Hiding Range Text?"
' Put your actual code here
Debug.Print HideValue.Address

Your choice on how you want to approach it.

Rick

 

[GerardV]

Check this page: http://www.jkp-ads.com/Articles/SelectARange.asp