Andi,

Well, my code works, but mostly through being lucky. I didn't use Option
Explicit (since you had a lot of variables, and I didn't want to declare
them all) and when you did declare variables, specifically Dim Const_Range
As Range, you never used it - due to a typo.

So when you used

const_raneg = Sheet1.Range("a" & Start_r & ":b" & last_r)

this worked, because it was a variant. If you had used Const_Range, it
would have failed - you would need to use

Set Const_Range = Sheet1.Range("a" & Start_r & ":b" & last_r)

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Andi,

Replace

Range(Var_Range).Select

with

If Application.CountIf(Sheet1.Range("A1", _
Sheet1.Cells(r, 1)), Var_Sheet) 1 Then
Union(Selection, Range(Var_Range)).Select
Else
Range(Var_Range).Select
End If

HTH,
Bernie
MS Excel MVP


"Andibevan" wrote in
message
...
Hi All,

The following macro loops through sheet names and cell ranges that are
located on sheet 1 of my spreadsheet and selects each range. On Sheet 1
Column A contains worksheets name and column b contains the range in the
following format:-
sheet2 a1:b2
sheet3 c1:d1
sheet3 c3:d3

The macro works fine as long as there is only 1 range to select per
worksheet. How do I modify the code to be able to select multiple
ranges
on
each worksheets (i.e. in the above data it would select both c1:d1 and
c3:d3
on sheet3).

Any pointers or advice would be greatfully received.

Ta

Andi


Sub try_select()
Dim Const_Range As Range

Dim Var_Sheet

Dim Var_Range

Start_r = 1 'Start Row

last_r = Sheet1.Range("A65536").End(xlUp).Row 'last row

const_raneg = Sheet1.Range("a" & Start_r & ":b" & last_r) 'Range
containing

information

r = Start_r

workcount = Worksheets.Count

last_sheet = Worksheets(workcount).Name

For Each Row In const_raneg

Sheet1.Activate

Var_Sheet = Cells(r, 1)

Var_Range = Cells(r, 2)

Worksheets(Var_Sheet).Activate

Range(Var_Range).Select

r = r + 1

If r = last_r + 2 - Start_r Then Exit For

Next Row

End Sub