0

I have a piece of code which is pretty straight forward:

Dim r As Integer, c As Integer
Dim rcnt As Integer, ccnt As Integer
With ActiveSheet

    .Unprotect
    Application.ScreenUpdating = False

    rcnt = .UsedRange.Rows.Count
    ccnt = .UsedRange.Columns.Count

    For r = 3 To rcnt
        For c = 1 To ccnt
            If Not .Cells(r, c).Locked Then
                .Cells(r, c) = ""
            End If
        Next
    Next

    Application.ScreenUpdating = True
    ThisWorkbook.ProtectSheet ActiveSheet

End With

It is run as part of a larger context where I manually shuffle stuff into several sheets from an external file. The really, really odd thing is that when I execute the larger set of procedures (of which this snippet is a part) it will be very, very slow (30-70 seconds). However, if I hit CTRL-BREAK, step into debug mode, and then immediately resume excecution, the code performs as expected, meaning sub-second time span for all consecutive sheets.

I'm posting here to see of someone has run across a similar behaviour, and if so, how did you fix it?

Thanks in advance!

/Martin Rydman

user2927704
  • 1
  • 1

2 Answers2

1

It happened the same to me and made me lose some hours. So, just for the record: when the execution stops, Excel processes every pending event in the spreadsheet, which can easely be blocked if you are updating a lot of cells. Adding a "DoEvents" command in key portions of the code solved the problem. It unblocks the spreadsheet and makes everything work "dramatically faster".

I hope this will save time to someone else...

Oscar GS
  • 11
  • 1
0

I've seen Excel behave oddly many times so I believe you. Most of the time I do not stop to diagnose but instead chose to code around or quite simply take a different strategy.

However, if I look at your code then perhaps we might be able to tighten it further.

On the line

.Cells(r, c) = ""

you are actually allocating a string, even though the string is empty. String allocation does take time. Perhaps you could use

.Cells(r, c).Clear

or

.Cells(r, c) = Empty

both of these have same impact as your line, i.e. to clear a cell. Will you try these and then see if you still have oddness?

Also you might want to consider clearing contiguous ranges in one statement. So

Range("B2:D4").Clear

will clear nine cells in one go, imagine if you could clear hundreds of cells in one go. I realise from your code that have cells that you do not want cleared which you are marking/protecting with the Locked property, but nevertheless I reckon you can write some more complex code to identify blocks of cells that can be cleared in one go.

And finally, it looks like you are constantly recycling your sheets, i.e. populating them and then clearing them down. You might want to instead start with a fresh sheet, put in your labels, lock any cells as necessary and then populate with variable data; actually come to think of it you could use a template sheet, see this link.

http://office.microsoft.com/en-gb/excel-help/about-excel-templates-HP005229286.aspx

S Meaden
  • 8,050
  • 3
  • 34
  • 65