What looping method is the fastest if you have nested loops? I've go
several ( For i = Range("A65536") To 1 Step -1 ) loops that tak
forever to run. Any suggestions on how to speed this up

--
Message posted from http://www.ExcelForum.com

What value do you keep in A65536?

In article ,
scain2004 wrote:

What looping method is the fastest if you have nested loops? I've got
several ( For i = Range("A65536") To 1 Step -1 ) loops that take
forever to run. Any suggestions on how to speed this up?


---
Message posted from http://www.ExcelForum.com/

I suspect you misunderstand what that means.

In my worksheet, where A65536 is blank, this following takes no time at
all, as it's tryinng to count down from 0 to 1.

Dim i As Long
For i = Range("A65536") To 1 Step -1
MsgBox "hello, world"
Next i

I suspect you are thinking in terms of looping throught the rows.

If so, the looping takes almost no time at all. It's what you do in the
loop that counts.

Try pasting the following sub into a module and run it, to see what I mean.

Sub test()
Dim i As Long
Dim msg$
msg$ = Now & vbLf
msg$ = Now & vbLf
For i = Range("A65536").Row To 1 Step -1
'do nothing
Next i
MsgBox msg$ & Now, , "Doing nothing"
'
msg$ = Now & vbLf
For i = Range("A65536").Row To 1 Step -1
Application.StatusBar = i
Next i
MsgBox msg$ & Now, , "Doing a little bit"
Application.StatusBar = False
End Sub



scain2004 < wrote:

What looping method is the fastest if you have nested loops? I've got
several ( For i = Range("A65536") To 1 Step -1 ) loops that take
forever to run. Any suggestions on how to speed this up?


---
Message posted from http://www.ExcelForum.com/

Hi .....,
If you tell Excel that you have 65536 rows it will take several minutes
just to process the cells in 1 column. You main problem is that you
are almost certainly processing non existent data.

Without knowing what you are doing can't really tell you much.
Some things (not many) can be done without loops.

Take a look at
Slow Response, Memory Problems, and Speeding up Excel
http://www.mvps.org/dmcritchie/excel/slowresp.htm
and
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm

Look particularly for such things as INTERSECT, SpecialCells,
turning off calculation and screen updating.

If you do not have data in your last row unless there is a value
in Column A also then you could use something like ...
Looping on items would be faster.

sub DoNothing()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim n As Long, i As Long
n = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "Starting up from Row " & n
For i = n To 1 Step -1 'more likely to 2 instead of 1
'...ooo ---- your code here ---- ooo
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"scain2004 " wrote
What looping method is the fastest if you have nested loops? I've got
several ( For i = Range("A65536") To 1 Step -1 ) loops that take
forever to run. Any suggestions on how to speed this up?

So, what's the content of A65535?

Suspect you're actually trying to loop for all rows in a sheet.
In which case whatever is in the For/Next loop is slowing things down. Could
you please supply?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"scain2004 " wrote in message
...
What looping method is the fastest if you have nested loops? I've got
several ( For i = Range("A65536") To 1 Step -1 ) loops that take
forever to run. Any suggestions on how to speed this up?


---
Message posted from http://www.ExcelForum.com/

My guess is you wanted to loop from the last used cell in column A to the first
row:

dim i as long
for i = range("a65536").end(xlup).row to 1 step -1

instead of
For i = Range("A65536").Value To 1 Step -1
or even
For i = 65536 To 1 Step -1





"scain2004 <" wrote:

What looping method is the fastest if you have nested loops? I've got
several ( For i = Range("A65536") To 1 Step -1 ) loops that take
forever to run. Any suggestions on how to speed this up?

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Sorry about the late response, had to go to bed.

What I'm doing is importing data from text files into new worksheets.
I'm setting up formatting and formulas per column, per row using fo
loops and if statements....many, many, many.

The amount of rows used is always different so I can't set an actua
end row for the loop. The way I've got it now works, but it's really
really slow.

I've saved the code to the enclosed text file

Attachment filename: codetext.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=50025
--
Message posted from http://www.ExcelForum.com

That's a lot of code.

Just some general tips. Drop the .selects.

Columns("G:G").Select
Selection.Style = "Comma"
Selection.ColumnWidth = 14.71

could become:

with columns("G:G")
.style = "Comma"
.columnwidth = 14.71
end with

if you're just changing the column width:
Columns("A:A").ColumnWidth = 11.43

==========

And when you're inserting a formula into a range, you can populate that whole
range in one fell swoop. Write your formula for the first cell in the range:

for i = 1 to Range("b65536").End(xlUp).Row
if i 3 then
cells(I,"H").formula = "=...."

could become:

range("H3:H" & range("b65536").end(xlup).row).formula _
= "=..."

=============

This kind of looping:

Select Case Cells(i, "b")
Case "10151": Cells(i, "c") = "Phoenix Sealing Department"
Case "10161": Cells(i, "c") = "Phoenix Asphalt Department"
Case "10171": Cells(i, "c") = "Phoenix Flexseal Department"

might be better with finds. Here's a skinnied down version as an example:

Dim myLookFors As Variant
Dim myReplacements As Variant
Dim iCtr As Long
Dim FoundCell As Range
Dim FirstAddress As String

myLookFors = Array("10151", "10161", "10171")
myReplacements = Array("Phoenix Sealing Department", _
"Phoenix Asphalt Department", _
"Phoenix FlexSeal Department")

For iCtr = LBound(myLookFors) To UBound(myLookFors)
With Range("b:b")
Set FoundCell = .Cells.Find(What:=myLookFors(iCtr), _
MatchCase:=False, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
FoundCell.Offset(0, 1).Value = myReplacements(iCtr)

Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With
Next iCtr

(This is pretty much a copy of the example from VBA's help.)

==========
Instead of looping through the worksheets to see if it exists, you could use a
function posted by Chip Pearson:

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = Len(WB.Worksheets(SheetName).Name) 0
End Function

===========
But I gotta believe the biggest time improvement would be to consolidate your
deletion of rows.

Does your data allow you to combine these things?

If Cells(i, "b") = "Job" Then
lRow = i
ElseIf Cells(i, "a") = "Sun" Then
sRow = i
End If
If sRow 0 And lRow 0 Then
Rows(sRow & ":" & lRow).Delete
sRow = 0
lRow = 0
End If

I used just two in this example:

If Cells(i, "b") = "Job" _
or Cells(i, "a") = "Curr" then
lRow = i
ElseIf Cells(i, "a") = "Sun" _
or Cells(i, "a") = "Sunl" Then
sRow = i
End If
If sRow 0 And lRow 0 Then
Rows(sRow & ":" & lRow).Delete
sRow = 0
lRow = 0
End If

I would bet that most the time is spent looping through the rows multiple
times. If you could combine it into one loop, it might be a bit quicker.

=======
And with this stuff, maybe a straight replace would work ok.

Select Case Cells(i, "c")
Case "101 Totals:":
Cells(i, "c") = "Phoenix Totals:"
Rows(i & ":" & i).Select
Selection.Font.ColorIndex = 5


Then record a macro when you apply data|filter|autofilter (for contains totals:)
to that C column. Edit|goto|special|visible cells only. And then format those
visible rows all at one time.

===
and if you turn calculation to manual, it could help (and turn it back to what
it was when you're done.)




"scain2004 <" wrote:

Sorry about the late response, had to go to bed.

What I'm doing is importing data from text files into new worksheets.
I'm setting up formatting and formulas per column, per row using for
loops and if statements....many, many, many.

The amount of rows used is always different so I can't set an actual
end row for the loop. The way I've got it now works, but it's really,
really slow.

I've saved the code to the enclosed text file.

Attachment filename: codetext.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=500255
---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson