Multiple Ifs Select

[ManUBlueJay]

I have a very complicated Question. The only way around that I can see is using 7 If,s
I am happy with a simple VBA formula or a way to name the ranges
I have seven different ranges, and depending on the users input I want to select given ranges.
If users selects Option 1 I want to select 7 ranges. A1:A4, A10:A14,A20:A24, A30:A34, A40:A44, A50:A54, A60:A64, A70:A74
If users selects Option 2 I want to select 6 ranges. All the options except the last.
If users selects Option 3 I want to select 5 ranges. All the options except the last2.

All the way up to user selecting option 7 and only A1:A4 would need selecting
I can't make a table and use a lookup.
Any help would be appreciated

 

[jasonb75]

See if this works

Sub otherway()
Dim rng As Range, rng2 As Range, a As Long, c As Long
Set rng = Range("A1:A4, A10:A14,A20:A24, A30:A34, A40:A44, A50:A54, A60:A64, A70:A74")
c = rng.Areas.Count
a = Range("B1").Value + 1
If a <= c Then
    Set rng2 = rng.Areas(a)
    Do Until a = c
        a = a + 1
        Set rng2 = Application.Union(rng2, rng.Areas(a))
    Loop
    rng2.Select
End If
End Sub

 

[mikerickson]

Hmmm

Dim optNum as Long
Dim FullRange as Range
Set FullRange = Range(""A1:A4, A10:A14,A20:A24, A30:A34, A40:A44, A50:A54, A60:A64, A70:A74")

optNum = Range("B1").Value

With FullRange
 
    Application.Intersect(.Cells, Range(.Cells(1,1), .Areas(optNum +1))).Select

End With

 

[ManUBlueJay]

Dim optNum as Long Dim FullRange as Range Set FullRange = Range(""A1:A4, A10:A14,A20:A24, A30:A34, A40:A44, A50:A54, A60:A64, A70:A74") optNum = Range("B1").Value With FullRange Application.Intersect(.Cells, Range(.Cells(1,1), .Areas(optNum +1))).Select End With

Works Brilliant. Do you have a suggestion for Post 10 to now select the ranges the come after B1

 

[mikerickson]

You still havn't explained what these ranges are being used for? Selecting discontinous ranges is probably not the best approach.

 

[ManUBlueJay]

Sorry I earlier posted I am reformatting them. So rest assured I am not going to use the select.
The format is complicated, I am only using you to show me how to select them, so I can finish with my code to finish the project.
Probably clear as mud but I was able to use what you posted for the first part

 

[jasonb75]

The format is complicated

In your opinion. Like the method of applying it, I expect that you're over complicating it. With the quality of your questions you're only getting answers at best, not solutions.