How do you use an input box to select a range?

[dee101]

This will work sometime and sometime it will not, what is the correct way to do this

Set SelRange = Application.InputBox("Select A Range", _
"Range Select", Type:=8)
SelRange.Select

 

[Tom Urtis]

One way:


Dim SelRange As Range
On Error Resume Next
Set SelRange = Application.InputBox("Select A Range", "Range Select", Type:=8)
If Err.Number = 424 Then
Err.Clear
Set SelRange = Nothing
MsgBox "No range was entered.", 48, "Cancelled"
Exit Sub
End If
MsgBox SelRange.Address, , "You selected:"

 

[dee101]

Tom, when I ran that sometime it show me the address and sometime it does not?

What I want to do is to let the user select the range to paste special valves, I thought I would use and input box for this and then just run this code, Is there a better way to do this?
Thanks

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 

[Tom Urtis]

This way is fine, the message box line for showing the address was just for purposes of this code. You can delete it if you want, and in its place, insert whatever code you were thinking of that was going to use the SelRange variable, becuase at that point it would have been defined by the proper selection of a range by using the inputbox as you did.

In your case this would suffice, given your appended example above:

With SelRange
.Value = .Value
End With


The entire macro would look like this then:


Sub DeeMacro()

Dim SelRange As Range
On Error Resume Next
Set SelRange = Application.InputBox("Select A Range", "Range Select", Type:=8)
If Err.Number = 424 Then
Err.Clear
Set SelRange = Nothing
MsgBox "No range was entered.", 48, "Cancelled"
Exit Sub
End If

With SelRange
.Value = .Value
End With
Set SelRange = Nothing

End Sub

 

[dee101]

Tom, Some of the cells have conditional formatting on them, this looks like it is not letting the code work, it will not show the address if you select a range that is formatted this way, is there away around this? formatting is
=MOD(ROW(),2)=0

 

[dee101]

Removed the formatting and it works fine, Thanks