How can I make this code faster? 2

[HorseGoose]

The worksheet has a grid containing raw materials, in order to format the worksheet correctly the first cell = "." if there is data in the row otherwise it is blank. The following code then goes through each row, if the row is blank it is hidden.

Other information, it is called from the activate sheet event and the screenupdating is set to false in there.

The code works fine, it is just glacially slow.


Sub bev_cogs_format()
' loops through the data lines which refer back the main beverage sheet
' if the line has nothing in it in terms of weight then it is hidden

' first unhide all the rows

Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Range("C18:C44").Select
Selection.EntireRow.Hidden = False

' then loop through each row and see if there is any weight entered, if not then hide the row
Dim loops As Integer
For loops = 18 To 66
Select Case Range("C" & loops)
Case Is = "."
Range("C" & loops).Select
Selection.EntireRow.Hidden = True
Case Else
' do nothing - do not hide the line because there is a weight
End Select
Next loops

' adds username and date and time the cogs sheet was generetae
Range("N5") = Environ("Username")
Range("N6") = Now()

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

 

[firefytr]

Thanks John.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[jackeroo75]

Why not use this

Application.ScreenUpdating = False ???


This speeds up the macro.

 

[strongm]

>Use a proper loop rather than a for next

Contrary to popular myth, this is incorrect. In VBA For Next is faster than Do or While loops

 

[AnotherHiggins]

Why not use this

Application.ScreenUpdating = False ???

From the original post:

...the screenupdating is set to false...

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[firefytr]

Look at the post I made 16 Sep 07 14:44, re a sub function..

Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

Use this to turn on/off events before/after running code.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[HorseGoose]

Thanks for all the comments, indeed events, screenupdating etc are toggled but that is done in the calling routine.

I tried do loop and for next and there is not that much difference at least its not noticable. Maybe it needs to be a long loop before you notice the difference.

Cheers

HG

 

[ck1999]

firefytr,
Just curious as why do you use the 2 if statements and not set the status bar and cutandcopy back to true

ck1999

 

[firefytr]

There is no need to set them to True. If you set them to False it 'resets' them. Hence a check to only run them when a True value is passed, will get them a False value at runtime. Follow? Maybe as an example you can copy a range, you'll get the dancing ants around the range, right? Now open the VBE/Immediate Window, type "application.cutcopymode = false", hit enter. What happens to your dancing ants? Yup, they're gone. Same with the StatusBar (more or less). Bottom line is they don't have a boolean (True/False) value base. I'm only using the False value to reset them, a byproduct.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[strongm]

>Maybe it needs to be a long loop before you notice the difference

You do indeed. For example on my single-core 3GHz PC it is of the order of only 0.04 milliseconds difference

 

[fumei]

strongm, is that time enough to finish your coffee?

Kind of a sidebar, but there was a thread here, back somewhere...maybe a year ago? It was on timing procedures, that is, code to accurately time things (like Do loops, For Next loops etc). If I recall there were some really good information, and links to API procedures for accurate timing.

I did a search but can not seem to find it. Anyone remember that one?

Never mind. I could not find the thread, but I did find one thread where Cajun Centurion mentions QueryPerformanceCounter, which is all I needed to search elsewhere. Ta.

faq219-2884

Gerry
My paintings and sculpture

 

[AnotherHiggins]

Gerry,

This probably isn't it since it was just posted a couple of months ago, but thread707-1399412 might be relevant.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[strongm]

>code to accurately time things (like Do loops, For Next loops etc). If I recall there were some really good information, and links to API procedures for accurate timing

I think that you may be thinking of my contribution in thread707-1179703