Inputbox and Union

[medwatt]

Hello,
How do I set a range such that when the user sees the input box he can select no contiguous cells. How can these cells be united into a simple range.

I tried this but no luck :

Set rng = Application.Union(Application.InputBox("Please select paramters:", Type:=8))

Your help is appreciated

 

[shg]

You don't need the Union:

    Set rng = Application.InputBox("Select some cells.", Type:=8)
    MsgBox rng.Address

 

[mikerickson]

If the user enters a comma between selecting ranges, the range returned by the InputBox will be discontinuous.

Dim uiRange As Range

On Error Resume Next
Set uiRange = Application.InputBox("Select a range, press comma to select discontinous range", Type:=8)
On Error GoTo 0

If uiRange Is Nothing Then
    Rem cancel pressed
Else
    MsgBox "You selected " & uiRange.Address
End If

 

[jim may]

How many different selections will you have? If two then maybe...

Sub Foo()
Set Rng1 = Application.InputBox("Please select parameter1", Type:=8)
Set Rng2 = Application.InputBox("Please select parameter2", Type:=8)
Set Rng = Application.Union(Rng1, Rng2)
End Sub

 

[shg]

Sorry, I should have added that the user needs to hold down the Ctrl key, the usual way for making multiple selections. The commas will be entered automatically.

 

[medwatt]

Thanks for the suggestions. My code is already written to access elements in the range (rng(i)). How should I modify it :

 For j = 3 To rng.Rows.Count
        If Application.Match(rng(j), wsHO.Rows(1), 0) Then
        Call blockArea(row, col, rng(j), dates)
            row = row + siteNo + 2
 End If

 

[RoryA]

You have to use a For Each loop:

Set rng = Application.InputBox("Please select paramters:", Type:=8)
For Each rngRow In rng.Rows
    Debug.Print rngRow.Address

 Next rngRow

The Cells and Range properties, when applied to a range object, are not restricted to cells within that parent range.