Forum Discussion
Getting the user to input a range into VBA
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!
4 Replies
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- 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, , , , , 😎
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?- 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
- Wow! Thats great! Thanks so much, this solves my problem perfectly! Many thanks
