Getting the user to input a range into VBA

Rory123908 

To ask a cell from the user is easiest using Application.InputBox:

Option Explicit

Function GetCellFromUser(prompt As String, title As String, default As Range) As Range
    Dim ans As Range
    On Error Resume Next
    Set ans = Application.InputBox(prompt, title, default.Address, , , , , 8)
    Set GetCellFromUser = ans
End Function

Sub Demo()
    Dim rng As Range
    Set rng = GetCellFromUser("Please select the start cell", "Selecting a start cell for the macro", ActiveCell)
    If rng Is Nothing Then
        'user cancelled
    Else
        'range was selected, now do something, like select all cells to the bottom of that range:
        rng.Resize(rng.End(xlDown).Row - rng.Row + 1).Select
    End If
End Sub