Not tested, but try

Dim prevSheet As String
dim rng As Range
For Each Row In const_raneg
Var_Sheet = Worksheets("Sheet1").Cells(r, 1).Value
Var_Range = Worksheets("Sheet1").Cells(r, 2).Value
if prevSheet = "" Then prevSheet = Var_Sheet
if prevSheet = Var_Sheet
If rng is nothing then
set rng = Range(Var_Range)
Else
set rng = Union(rng, Range(Var_Range))
End If
Else
Worksheets(Var_Sheet).Activate
rng.Select
End If
r = r + 1

If r = last_r + 2 - Start_r Then Exit For

Next Row


--

HTH

RP
(remove nothere from the email address if mailing direct)


"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