InputBox selects rows

Dave T · #1 08-31-2016, 06:09 PM

Hello All,

Not quite sure if an InputBox is the right approach, but I am at a loss on how to do this.

I have a range of data between columns A to K and I have been looking for a way for a user to use something like an InputBox the select any range of cells within a column and the data between columns A to K is selected.

Basically if a user selects a range of cells in any column between A to K the macro will select the data in the rows between columns A to K.

It would be nice if a user selected a range of cells between columns A to K it would still select all the data between A to K

If A10:A20 is selected via the InputBox then A10:K20 will be selected
If G10:G20 is selected via the InputBox then A10:K20 will be selected
If A10:E20 is selected via the InputBox then A10:K20 will be selected

Regards,
Dave T

NoSparks · #2 09-01-2016, 08:14 AM

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

p45cal · #3 09-02-2016, 05:34 AM

Without row and column restrictions, try adapting NoSparks' code:

Code:

Sub blah()
On Error Resume Next
Set PickedRng = Application.InputBox("Use mouse to select", "TITLE HERE", Type:=8)
Intersect(PickedRng.EntireRow, Range("A:K")).Select
End Sub

NoSparks, note what happens with your code when a single cell is selected…

However, the OP did say "if a user selects a range of cells in any column between A to K" so perhaps:

Code:

Sub blah2()
Set MainRng = Range("A:K")
On Error Resume Next
Set PickedRng = Application.InputBox("Use mouse to select", "TITLE HERE", Type:=8)
If Application.Intersect(PickedRng, MainRng) Is Nothing Then
  MsgBox "Something within columns A to K please"
  Cells(1).Select
  Exit Sub
End If
Intersect(PickedRng.EntireRow, Range("A:K")).Select
End Sub'

NoSparks · #4 09-02-2016, 12:03 PM

Yup, that's something along the lines of...... thanks for tuning it up.
Now if only the OP ever comes back.

Dave T · #5 09-04-2016, 05:25 PM

Hello NoSparks and p45cal,

I really appreciate both of your replies and sorry for not replying sooner.

I decided to take the kids climbing for a couple of days. Thursday night we left Adelaide for a quick drive to Mount Arapiles (one of the best cliffs in Australia that is in Victoria) and only got back late Sunday night.

I did find some examples that selected a row from cell A to K, but could not work out how to get this to work on more than one row.

Code:

 
Sub SelectRow()
'http://stackoverflow.com/questions/16394036/select-column-1-to-10-of-activecell-row-in-excel

Range("A" & ActiveCell.Row).Resize(1, 11).Select
'OR
ActiveSheet.Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 11)).Select
'OR
Range("A" & ActiveCell.Row & ":K" & ActiveCell.Row).Select

End Sub

I did not want to be restricted to a fixed range as my example showed (maybe my question was poorly worded) and what p45cal provided suited my needs perfectly. Both of his examples are good but technically the 'blah2' option where cell selection between the two columns is required is the best.

Thanks again to both of you for your responses.

Regards,
Dave T

Dave T · #6 09-05-2016, 06:58 AM

Hello All, just thought i'd post another solution I found provided by Rick Rothstein in another forum.

Code:

Sub Rows_between_columns_A_and_K()
'http://www.mrexcel.com/forum/excel-questions/891421-restrict-range-input-box.html

  Dim Rng As Range
  
  On Error GoTo NoRangeSelected
  Set Rng = Application.InputBox("Select range spanning Columns A:K.", Type:=8)
  If Rng(1).Column <> 1 Or Rng.Columns.Count <> 11 Then
    MsgBox "You selected range """ & Rng.Address(0, 0) & """ which does not span Columns A:K!", vbCritical
  Else
    '
    ' The selected range is good, so do whatever you want with it here
    Rng.Select
    '
  End If
  Exit Sub
NoRangeSelected:
  MsgBox "No range selected!", vbCritical
End Sub

Thanks NoSparks and p45cal for taking the time to answer my question.

Regards,
Dave T

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MS Word 2010 Table Selector Selects Header On One Page and Rows On Next Page	scptech	Word Tables	4	01-11-2016 02:55 PM
VBA Macro for word with Inputbox	ryanch69	Word VBA	2	12-03-2015 07:45 AM
Idiot Proof Entry with InputBox	arpirnat	Word VBA	1	04-27-2015 10:03 PM
Is there a way to change how Excel auto selects where it pulls data?	PStark	Excel	0	12-30-2014 09:28 AM
Cannot select single characters. Word selects paragraphs or parts of sentences.	TMinnich	Word	2	10-25-2013 09:38 AM