Sheet suddently slow

[miami2k]

Dear All,

I'm working a quite big process with a userform with 8/10 buttons doing different things on a sheet.
The file il 1400 kb big and it used to be normally fast.
Some weeks ago all the processes suddently slowed down but I can't understand why and i don't know where to start to find out.
If I go though the macros with F8 all the steps are quite quick unless there are operations on the cells.
For example this command takes like 3 seconds: Cells(riga, 3) = "Annullato"This is ridiculus. Deleting a row take 10 seconds.
It is normal that a macro that does simple things of a sheet takes minutes when some weeks ago in 10 seconds was done.

Any clue to solve this is issue.

Can I copy the modules in a new sheet? Can I delete part of the sheet to see if slow?


Thank you very much.


miami2k

 

[rlv01]

Easy enough to test for that. Run it on another PC. Perhaps something is going on in the background on your PC? Something like a weekly antivirus scan, or window rebuilding your file index from scratch.

 

[miami2k]

Done already. It is faster on faster machines but still slow also and my office PC that is very performing. If I stay under 1000 rows is fast also on my laptop that is 10 years old.

 

[JonXL]

Sometimes even "blank" cells contain information like formatting, etc.

One thing to try (and I've used this successfully myself) is to select all the cells below the last row with data and do a "Clear All" operation on them and then do the same to all the cells to the right of the last column with data.

Save. Reopen.

 

[MARK858]

Sometimes even "blank" cells contain information like formatting, etc.

One thing to try (and I've used this successfully myself) is to select all the cells below the last row with data and do a "Clear All" operation on them and then do the same to all the cells to the right of the last column with data.

Save. Reopen.

Should use Delete rather ways than Clear as using Clear doesn't always alter what Excel sees as the usedrange.

With VBA...

[color=darkblue]Sub[/color] LoseThatWeight()

    [color=darkblue]Dim[/color] X [color=darkblue]As[/color] [color=darkblue]Long[/color], LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color], LastCol [color=darkblue]As[/color] [color=darkblue]Long[/color]

    Application.ScreenUpdating = [color=darkblue]False[/color]

    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]

    [color=darkblue]For[/color] X = 1 [color=darkblue]To[/color] Sheets.Count
        [color=darkblue]With[/color] Sheets(X)
            LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] X
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Test on a copy of the workbook as you are deleting