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

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

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

Bob,

I presume that a pre-requisite of your approach is for the sheets in
sequence. i.e. if the list had 3 entries for sheet1, an entry for

"Bob Phillips" wrote in message
...
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

Hello Andibevan,

For myself I see 2 possibilities:

A. For Sheet3, Column B should contain a string like "c1:d1,c3:d3" or
"c1:d1;c3:d3" (depending on your local setting);
in this case the complete range is selected at once by
Range(Var_Range).Select

B. If this is not possible, you could construct this string in code, by
adding a subloop
in which - as long as the sheet name in column A remains the same - the
value
of column is added to the former string, divided by ',' or ';'


In addition may I give some 'grammatical' hints?

- I think the line ' Const_Range = Sheet1.Range("a" & Start_r & ":b" &
last_r) '
should be preceded by the key word 'Set' in order to create a range object

- the number of rows in Sheet1 could also be determined by the statement:
last_r = Sheet1.Range("A1").CurrentRange (or whereever you start)

- your For/Next-loop could be more easily read (and with less lines of code)
when you would have written:
For r = 1 to last_r 'r is automatically initiated here

' (etc.)
' you don't need an If-statement here to test r

Next

- you'd better use ' Cells(r, 1).Value ' for ' Cells(r, 1) ' ,
although Value is the default property for a Range-object

- you may skip one line of code (and you get quicker execution of the code),
while you need not select a worksheet to extract data from it:
' skip Sheet1.Activate
Var_Sheet = Sheet1.Cells(r, 1)
Var_Range = Sheet1.Cells(r, 2)

Please do apologize these teaching inclinations!
Many greetings,
Peter

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

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

Bernie,

I sent my other reply before I spotted this note.

Interestingly - I managed somehow to be consistent with my typo (to some
extent anyway)

Regards

Andy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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

Peter,

Thanks for pointing out all of those things to me. I have been teaching
myself VBA and there are some basic fundamentals that I have managed not to
learn, despite understanding many slightly more complex principles.

Always glad to receive a bit of free tuition.

Many thanks

Andi

"Peter_A_M (NL)" wrote in message
...
Hello Andibevan,

For myself I see 2 possibilities:

A. For Sheet3, Column B should contain a string like "c1:d1,c3:d3" or
"c1:d1;c3:d3" (depending on your local setting);
in this case the complete range is selected at once by
Range(Var_Range).Select

B. If this is not possible, you could construct this string in code, by
adding a subloop
in which - as long as the sheet name in column A remains the same - the
value
of column is added to the former string, divided by ',' or ';'


In addition may I give some 'grammatical' hints?

- I think the line ' Const_Range = Sheet1.Range("a" & Start_r & ":b" &
last_r) '
should be preceded by the key word 'Set' in order to create a range object

- the number of rows in Sheet1 could also be determined by the statement:
last_r = Sheet1.Range("A1").CurrentRange (or whereever you start)

- your For/Next-loop could be more easily read (and with less lines of code)
when you would have written:
For r = 1 to last_r 'r is automatically initiated here

' (etc.)
' you don't need an If-statement here to test r

Next

- you'd better use ' Cells(r, 1).Value ' for ' Cells(r, 1) ' ,
although Value is the default property for a Range-object

- you may skip one line of code (and you get quicker execution of the code),
while you need not select a worksheet to extract data from it:
' skip Sheet1.Activate
Var_Sheet = Sheet1.Cells(r, 1)
Var_Range = Sheet1.Cells(r, 2)

Please do apologize these teaching inclinations!
Many greetings,
Peter

Sorry Bob, I hit send before finishing my mail - please ignore the bit
"i.e. if the list had 3 entries for sheet1, an entry for"

"Andibevan" wrote in message
...
Bob,

I presume that a pre-requisite of your approach is for the sheets in
sequence. i.e. if the list had 3 entries for sheet1, an entry for

"Bob Phillips" wrote in message
...
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

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