#1
|
|||
|
|||
InputBox selects rows
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
Dave T |
#2
|
|||
|
|||
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 |
#3
|
||||
|
||||
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 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' |
#4
|
|||
|
|||
Yup, that's something along the lines of...... thanks for tuning it up.
Now if only the OP ever comes back. |
#5
|
|||
|
|||
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 Thanks again to both of you for your responses. Regards, Dave T Last edited by Dave T; 09-05-2016 at 07:02 AM. |
#6
|
|||
|
|||
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 Regards, Dave T |
Thread Tools | |
Display Modes | |
|
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 |