Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop on visible cells only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop on visible cells only
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop on visible cells only
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. -- MattShoreson ------------------------------------------------------------------------ MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472 View this thread: http://www.excelforum.com/showthread...hreadid=536290 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop on visible cells only
Are you able to give sample of r is
NOT NOTHING( an if structure). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Visible Cells in Sheet with Merged and Hidden Cells | Excel Discussion (Misc queries) | |||
Sum Visible Cells | Excel Worksheet Functions | |||
AutoFilter: Loop Thru Visible Rows? | Excel Programming | |||
Loop thru multiple files - Modify worksheet visible property | Excel Programming | |||
Help: Copying Visible Cells only to Visible cells! | Excel Programming |