View Single Post
 
Old 09-01-2016, 08:14 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Maybe something along the lines of this
Code:
Sub Testing()

    Dim MainRng As Range
    Dim PickedRng As Range
    Dim arr As Variant
    
Set MainRng = Range("A10:K20")

On Error Resume Next
Set PickedRng = Application.InputBox("Use mouse to select", "TITLE HERE", Type:=8)
On Error GoTo 0

If PickedRng Is Nothing Then Exit Sub

If Application.Intersect(PickedRng, MainRng) Is Nothing Then
    MsgBox "Something within rows 10 to 20 please"
    Cells(1).Select
    Exit Sub
End If

Set PickedRng = Application.Intersect(PickedRng, MainRng)
arr = Split(Replace(Mid(PickedRng.Address, 2), ":", ""), "$")
Range("A" & arr(1) & ":K" & arr(3)).Select

End Sub
Reply With Quote