Help with - Sheets(Array(1, 2, 3)).Select
 

I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.

Help with - Sheets(Array(1, 2, 3)).Select
 

Sub test()
Dim arr() As Long
Dim i As Long, n As Long

ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1))

n = 1
For i = 2 To 45
arr(n) = i
n = n + 1
Next

For i = 60 To 99
arr(n) = i
n = n + 1
Next

Sheets(arr).Select

End Sub

I didn't test as written above but something similar with a smaller number
of sheets worked.

Regards,
Peter T


"excelent" wrote in message
...
I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.

Help with - Sheets(Array(1, 2, 3)).Select
 

excelent job Peter thanks :-)


"Peter T" skrev:

Sub test()
Dim arr() As Long
Dim i As Long, n As Long

ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1))

n = 1
For i = 2 To 45
arr(n) = i
n = n + 1
Next

For i = 60 To 99
arr(n) = i
n = n + 1
Next

Sheets(arr).Select

End Sub

I didn't test as written above but something similar with a smaller number
of sheets worked.

Regards,
Peter T


"excelent" wrote in message
...
I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.

Help with - Sheets(Array(1, 2, 3)).Select
 

... is it possible to just add say from 2-45 and 60 to 99 ?

Just for the fun challenge of not using loops...

Sheets([Transpose(Row(2:45))]).Select
Sheets([Transpose(Row(60:99))]).Select (False)

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"excelent" wrote in message
...
excelent job Peter thanks :-)


"Peter T" skrev:

Sub test()
Dim arr() As Long
Dim i As Long, n As Long

ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1))

n = 1
For i = 2 To 45
arr(n) = i
n = n + 1
Next

For i = 60 To 99
arr(n) = i
n = n + 1
Next

Sheets(arr).Select

End Sub

I didn't test as written above but something similar with a smaller
number
of sheets worked.

Regards,
Peter T


"excelent" wrote in message
...
I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.

Help with - Sheets(Array(1, 2, 3)).Select
 

Dana,
That is very clever and not something I would have ever thought of.
I did discover that it throws a Type Mismatch error if R1C1 reference
style is used.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware





"Dana DeLouis"
wrote in message
... is it possible to just add say from 2-45 and 60 to 99 ?
Just for the fun challenge of not using loops...

Sheets([Transpose(Row(2:45))]).Select
Sheets([Transpose(Row(60:99))]).Select (False)

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"excelent"
wrote in message
excelent job Peter thanks :-)


"Peter T" skrev:

Sub test()
Dim arr() As Long
Dim i As Long, n As Long

ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1))

n = 1
For i = 2 To 45
arr(n) = i
n = n + 1
Next

For i = 60 To 99
arr(n) = i
n = n + 1
Next

Sheets(arr).Select

End Sub

I didn't test as written above but something similar with a smaller
number
of sheets worked.

Regards,
Peter T


"excelent"
wrote in message
I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.

Help with - Sheets(Array(1, 2, 3)).Select
 

I did discover that it throws a Type Mismatch error if R1C1 reference
style is used.

Hi Jim. Thanks for the feedback on the R1C1 reference!
One option if using R1C1 would be to add an "R" in front of the row numbers.

Sheets([Transpose(Row(R2:R45))]).Select
Sheets([Transpose(Row(R60:R99))]).Select False

--
Thanks again. :)
Dana DeLouis
Windows XP & Office 2003


"Jim Cone" wrote in message
...
Dana,
That is very clever and not something I would have ever thought of.
I did discover that it throws a Type Mismatch error if R1C1 reference
style is used.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware





"Dana DeLouis"
wrote in message
... is it possible to just add say from 2-45 and 60 to 99 ?
Just for the fun challenge of not using loops...

Sheets([Transpose(Row(2:45))]).Select
Sheets([Transpose(Row(60:99))]).Select (False)

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"excelent"
wrote in message
excelent job Peter thanks :-)


"Peter T" skrev:

Sub test()
Dim arr() As Long
Dim i As Long, n As Long

ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1))

n = 1
For i = 2 To 45
arr(n) = i
n = n + 1
Next

For i = 60 To 99
arr(n) = i
n = n + 1
Next

Sheets(arr).Select

End Sub

I didn't test as written above but something similar with a smaller
number
of sheets worked.

Regards,
Peter T


"excelent"
wrote in message
I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.

Help with - Sheets(Array(1, 2, 3)).Select
 

Hi Dana,

Pretty cute :-)

Would you know how to make an array that way without hard-coding the values.

a = 2: b = 4
Arr = [transpose(row(a:b))]

Arr becomes 1 to 65536 populated with respective element numbers and
regardless of the values a & b.

Regards,
Peter T


"Dana DeLouis" wrote in message
...
... is it possible to just add say from 2-45 and 60 to 99 ?

Just for the fun challenge of not using loops...

Sheets([Transpose(Row(2:45))]).Select
Sheets([Transpose(Row(60:99))]).Select (False)

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"excelent" wrote in message
...
excelent job Peter thanks :-)


"Peter T" skrev:

Sub test()
Dim arr() As Long
Dim i As Long, n As Long

ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1))

n = 1
For i = 2 To 45
arr(n) = i
n = n + 1
Next

For i = 60 To 99
arr(n) = i
n = n + 1
Next

Sheets(arr).Select

End Sub

I didn't test as written above but something similar with a smaller
number
of sheets worked.

Regards,
Peter T


"excelent" wrote in message
...
I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.

Help with - Sheets(Array(1, 2, 3)).Select
 

Sub ABC()
Dim a As Long, b As Long
a = 2: b = 4
arr = Evaluate("transpose(row(" & _
a & ":" & b & "))")
MsgBox "Arr: " & arr(LBound(arr)) & "-" & arr(UBound(arr))

End Sub


--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Hi Dana,

Pretty cute :-)

Would you know how to make an array that way without hard-coding the
values.

a = 2: b = 4
Arr = [transpose(row(a:b))]

Arr becomes 1 to 65536 populated with respective element numbers and
regardless of the values a & b.

Regards,
Peter T


"Dana DeLouis" wrote in message
...
... is it possible to just add say from 2-45 and 60 to 99 ?

Just for the fun challenge of not using loops...

Sheets([Transpose(Row(2:45))]).Select
Sheets([Transpose(Row(60:99))]).Select (False)

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"excelent" wrote in message
...
excelent job Peter thanks :-)


"Peter T" skrev:

Sub test()
Dim arr() As Long
Dim i As Long, n As Long

ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1))

n = 1
For i = 2 To 45
arr(n) = i
n = n + 1
Next

For i = 60 To 99
arr(n) = i
n = n + 1
Next

Sheets(arr).Select

End Sub

I didn't test as written above but something similar with a smaller
number
of sheets worked.

Regards,
Peter T


"excelent" wrote in message
...
I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.

Help with - Sheets(Array(1, 2, 3)).Select
 

Ah, of course. Like passing arguments with Ontime.
Thanks for that.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Sub ABC()
Dim a As Long, b As Long
a = 2: b = 4
arr = Evaluate("transpose(row(" & _
a & ":" & b & "))")
MsgBox "Arr: " & arr(LBound(arr)) & "-" & arr(UBound(arr))

End Sub


--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Hi Dana,

Pretty cute :-)

Would you know how to make an array that way without hard-coding the
values.

a = 2: b = 4
Arr = [transpose(row(a:b))]

Arr becomes 1 to 65536 populated with respective element numbers and
regardless of the values a & b.

Regards,
Peter T


"Dana DeLouis" wrote in message
...
... is it possible to just add say from 2-45 and 60 to 99 ?

Just for the fun challenge of not using loops...

Sheets([Transpose(Row(2:45))]).Select
Sheets([Transpose(Row(60:99))]).Select (False)

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"excelent" wrote in message
...
excelent job Peter thanks :-)


"Peter T" skrev:

Sub test()
Dim arr() As Long
Dim i As Long, n As Long

ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1))

n = 1
For i = 2 To 45
arr(n) = i
n = n + 1
Next

For i = 60 To 99
arr(n) = i
n = n + 1
Next

Sheets(arr).Select

End Sub

I didn't test as written above but something similar with a smaller
number
of sheets worked.

Regards,
Peter T


"excelent" wrote in message
...
I can select several sheets with
Sheets(Array(1, 2, 3)).Select
but if i ned to select many sheets, say abot 100 how do i add the
array to do that without writing 1,2,3,4,5,6,7,... manualy ?

by the way is it possible to just add say from 2-45 and 60 to 99 ?

I cant figure this out,
so any help would be greatfull
thanks in advance.