Andi,

The CountIf is looking for uniqueness, but in a special way: has the name
been used before in the loop, not that it is unique in the list. The first
time that the sheet is activated, it will have a selection, and you don't
want to include that selection in the Union statement, but you do want to
include that range in subsequent visits.

Union(selection, range(var_range)).select

works similar to using Ctrl-mouse clicks: it basically extends the existing
selection (selection) by adding the range(var_range) into the selection.

As for your being lucky with your mis-typing, the best thing to do is to get
into the habit of always using Option Explicit (which forces you to declare
your variables) (in the VBE use Tools Options Editor tab, check "require
variable declaration") and always using variable names that have some caps:
Var_Range instead of var_range. Then, when you type your variable, type it
only in lower case. If you have correctly typed it, it will change to the
same capitilization as in the dimensioning statement.

HTH,
Bernie
MS Excel MVP


"Andibevan" wrote in message
...
Bernie,

Thanks so much for this - I was trying to use the Union command but to no
avail.

Is there any chance you could briefly explain how the If statement you
have
used works.

I understand that it counts to see if the sheet name in column A is unque,
but I don't understand how the statement Union(selection,
range(var_range)).select works?

Thanks

Andy


"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