loop on visible cells only

[Sliman]

I have following code that i wish to run on visible cells only.
the way i have it is slow can someone guide me in a better way to run
code on visible cells only.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Check = True: counter = 0: r = 50 ' Initialize variables.
Do ' Outer loop.
Do While counter < 1137 ' Inner loop.
r = 50 + counter 'start row
c = 14 'start Column

CloseStk = Cells(r, c).Value
calczero = Cells(r - 2, c).Value - Cells(r - 3, c - 2).Value
MinWks = Val(Cells(r - 3, 11).Value) / 4
ERQ = Cells(r - 3, 9).Value
Demand = Cells(r - 2, c).Value
Demand1 = Cells(r - 2, c + 1).Value
Demand2 = Cells(r - 2, c + 2).Value
Demand3 = Cells(r - 2, c + 3).Value
Demand4 = Cells(r - 2, c + 4).Value
MthsC = Cells(r - 3, 10).Value
Prod = Cells(r - 1, c).Value
OpenStk = Cells(r, c - 1).Value

If Cells(r, c).EntireRow.Hidden = False Then

If Cells(r - 3, 1).Value = "Y" And Cells(r - 3, 2).Value = 1 Then

If CloseStk = 0 Then
If MthsC = 1 Then
If Demand1 > 0 Then
Cells(r - 1, c) = calczero + (Demand1 * MinWks)
Else: Cells(r - 1, c).Value = calczero
End If

ElseIf MthsC = 2 Then

...... code goes on and on ........

End If

End If
End If
End If
counter = counter + 4 ' Increment Counter.
If counter > 1137 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


thanks for any help
Simon

[Gary''s Student]

Early in the code:

Dim R as Range
Set r=ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)

then later in the code check that the INTERSECTION() of your cell with r is
NOT NOTHING( an if structure). This will skip invisible cells
--
Gary's Student


"Sliman" wrote:

> I have following code that i wish to run on visible cells only.
> the way i have it is slow can someone guide me in a better way to run
> code on visible cells only.
>
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
>
> Check = True: counter = 0: r = 50 ' Initialize variables.
> Do ' Outer loop.
> Do While counter < 1137 ' Inner loop.
> r = 50 + counter 'start row
> c = 14 'start Column
>
> CloseStk = Cells(r, c).Value
> calczero = Cells(r - 2, c).Value - Cells(r - 3, c - 2).Value
> MinWks = Val(Cells(r - 3, 11).Value) / 4
> ERQ = Cells(r - 3, 9).Value
> Demand = Cells(r - 2, c).Value
> Demand1 = Cells(r - 2, c + 1).Value
> Demand2 = Cells(r - 2, c + 2).Value
> Demand3 = Cells(r - 2, c + 3).Value
> Demand4 = Cells(r - 2, c + 4).Value
> MthsC = Cells(r - 3, 10).Value
> Prod = Cells(r - 1, c).Value
> OpenStk = Cells(r, c - 1).Value
>
> If Cells(r, c).EntireRow.Hidden = False Then
>
> If Cells(r - 3, 1).Value = "Y" And Cells(r - 3, 2).Value = 1 Then
>
> If CloseStk = 0 Then
> If MthsC = 1 Then
> If Demand1 > 0 Then
> Cells(r - 1, c) = calczero + (Demand1 * MinWks)
> Else: Cells(r - 1, c).Value = calczero
> End If
>
> ElseIf MthsC = 2 Then
>
> ...... code goes on and on ........
>
> End If
>
> End If
> End If
> End If
> counter = counter + 4 ' Increment Counter.
> If counter > 1137 Then ' If condition is True.
> Check = False ' Set value of flag to False.
> Exit Do ' Exit inner loop.
> End If
> Loop
> Loop Until Check = False ' Exit outer loop immediately.
>
> Application.Calculation = xlCalculationAutomatic
> Application.ScreenUpdating = True
>
>
> thanks for any help
> Simon
>
>

[MattShoreson]

why the outer loop?

nested ifs are probably slowing this down.
Try using select case statements instead of many ifs.

Is there no way of combining these statements...
If Cells(r, c).EntireRow.Hidden = False Then
If Cells(r - 3, 1).Value = "Y" And Cells(r - 3, 2).Value = 1 Then If CloseStk = 0 Then

try inserting the data to an array, process the data in memory and write it back?

Many ways of solving the same problem. Would probably be easier to solve with a sample dataset.

thx,
Matt.

[Sliman]

Are you able to give sample of r is
NOT NOTHING( an if structure).