Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-31-2016, 06:09 PM
Dave T Dave T is offline InputBox selects rows Windows 7 64bit InputBox selects rows Office 2007
Advanced Beginner
InputBox selects rows
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default 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
  • 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
Reply With Quote
  #2  
Old 09-01-2016, 08:14 AM
NoSparks NoSparks is offline InputBox selects rows Windows 7 64bit InputBox selects rows Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #3  
Old 09-02-2016, 05:34 AM
p45cal's Avatar
p45cal p45cal is offline InputBox selects rows Windows 10 InputBox selects rows Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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'
Reply With Quote
  #4  
Old 09-02-2016, 12:03 PM
NoSparks NoSparks is offline InputBox selects rows Windows 7 64bit InputBox selects rows Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Yup, that's something along the lines of...... thanks for tuning it up.
Now if only the OP ever comes back.
Reply With Quote
  #5  
Old 09-04-2016, 05:25 PM
Dave T Dave T is offline InputBox selects rows Windows 7 64bit InputBox selects rows Office 2007
Advanced Beginner
InputBox selects rows
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

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

Last edited by Dave T; 09-05-2016 at 07:02 AM.
Reply With Quote
  #6  
Old 09-05-2016, 06:58 AM
Dave T Dave T is offline InputBox selects rows Windows 7 64bit InputBox selects rows Office 2007
Advanced Beginner
InputBox selects rows
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
InputBox selects rows 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
InputBox selects rows 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
InputBox selects rows Cannot select single characters. Word selects paragraphs or parts of sentences. TMinnich Word 2 10-25-2013 09:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft