Getting the user to input a range into VBA

[Rory123908]

Hey there, I'm looking to do a text to columns transformation within VBA. The data will be coming in various different forms. It will be all on column A, however the data/range we want to capture will be will be starting on various rows. For example one day the data may start on row 757 and the next day it may start on row 1214. I'm looking for a way to incorporate this into my VBA macro. I'd like it to ask the user "what row does the data start on" and If the user types 757, then I'd like the macro to select the range from 757 to the bottom of the spreadsheet. Is this possible and how would you go about doing this? Thanks very much guys!

[#2]

@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

[#3]

Wow! Thats great! Thanks so much, this solves my problem perfectly! Many thanks

[#4]

Hi again, Ive added a line of code to your example to try to solve my problem. Here is the full code: 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
rng.TextToColumns , xlDelimited, xlDoubleQuote, True, , , , True


End If

End Sub

It is the part about texttocolumns! For some reason this only applies it to the first cell in the list, and does not apply to all selected cells, do you have any idea why it does this and how I may fix it?

[#5]

Change this line:
rng.Resize(rng.End(xlDown).Row - rng.Row + 1).Select
to:
Set rng = rng.Resize(rng.End(xlDown).Row - rng.Row + 1)
then it should work