I have a code that loops through 56 Worksheets and on each sheet loops
through the used cells in Column A. Does anyone have a way to execute
the two-phase looping without activating the Worksheets? Following the
line of least resistance, and at the expense of speed, I have:

For each sh in WorkSheets
sh.activate
For each c in Range("a:a")
If Not IsEmpty(c) Then

<Do something

End if
Next
Next


I reckon that the code could be vastly speeded up if it didn't have to
select each sheet.

TIA.

Myles.


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Myles,

Try:

Public Sub Tester03()
Dim SH As Worksheet
Dim c As Range
Dim CalcMode As Long

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each SH In Worksheets
For Each c In SH.Range("a:a")
If Not IsEmpty(c) Then
'<Do something
End If
Next c
Next SH

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============


---
Regards,
Norman


"Myles" wrote in
message ...

I have a code that loops through 56 Worksheets and on each sheet loops
through the used cells in Column A. Does anyone have a way to execute
the two-phase looping without activating the Worksheets? Following the
line of least resistance, and at the expense of speed, I have:

For each sh in WorkSheets
sh.activate
For each c in Range("a:a")
If Not IsEmpty(c) Then

<Do something

End if
Next
Next


I reckon that the code could be vastly speeded up if it didn't have to
select each sheet.

TIA.

Myles.


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi,

I hope, it would help you out. Instead of using collection, you could
use a simple loop. Code could be used as follows:

For x = 1 To Sheets.Count
If Sheets(x).Range("___") = "Validation" Then

your code here...

End If
Next


--
gajendra_vba
------------------------------------------------------------------------
gajendra_vba's Profile: http://www.excelforum.com/member.php...o&userid=29935
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Norman,

Many hanks. Your code does the trick!

I had tried something similar but yours has exposed my mistake. For the
<do something segment of the code, I had a Call statement which
invoked another subroutine . And since this subroutine didn't have
range references qualified by the sheet reference, SH in this case, I
ran into strife as only the activesheet was worked upon. To get round
the problem, I have now abandoned the Call idea and embedded the
subroutine in the main code with all range references duly tacked. This
makes the end-product code very unwieldy but I suppose that is the price
to pay for enhanced speed.

In any event, if you know of a way I can use the Call idea -without-
-activating -the individual sheets, I would welcome it. I flirted with
declaring SH global-to no avail, apparently because SH is an object not
a variable.


Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Another one that failed ...
Passing the current sheet as an argument, as in:

Sub LoopThruShtsIn2Phases()
Dim sh as Worksheet

For each sh in worksheets
Call DoSomething(sh)
Next

End Sub

Sub DoSomething(obSht) *'returns Object doesn't support this property
or method *error
Dim c as range

For each c in obSht.Range("a:a")
Is Not Isempty(c) Then

<CarryOutSomeActions

End if
Next
End Sub


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Myles,

'=============
Public Sub Tester03()
Dim SH As Worksheet
Dim c As Range
Dim CalcMode As Long

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each SH In Worksheets
For Each c In SH.Range("A1:A20") '<<==== CHANGE
If Not IsEmpty(c) Then
Call MyMacro(c) '<<==== CHANGE
End If
Next c
Next SH

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

'=============
Sub MyMacro(rng As Range)
rng.Interior.ColorIndex = 6
End Sub
'<<=============

--
---
Regards,
Norman



"Myles" wrote in
message ...

Another one that failed ...
Passing the current sheet as an argument, as in:

Sub LoopThruShtsIn2Phases()
Dim sh as Worksheet

For each sh in worksheets
Call DoSomething(sh)
Next

End Sub

Sub DoSomething(obSht) *'returns Object doesn't support this property
or method *error
Dim c as range

For each c in obSht.Range("a:a")
Is Not Isempty(c) Then

<CarryOutSomeActions

End if
Next
End Sub


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Norman,

Many Thanks again. Great solution!

I found it a bit perplexing though why passing the cell range (c in
the example) as an argument works hitch-free but passing a sheet object
(sh as in my example) throws up error message.

If there is an underlying reason why this is so it would be interesting
to know.

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613