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: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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