Excel WB painfully slow

[klandreth]

I have an Excel workbook that contains three sheets: 1) Instructions, 2) "Dashboard", 3) data spreadsheet. NO calculations are done on any of these pages. All work is done behind the scenes using VBA code.

The data sheet is currently 47 columns (never changes) by 25,400 rows (dynamic). Up until this week, things were great, but now doing something as simple as moving from one cell to the next is so painfully slow as to make the entire workbook nonfunctional.:banghead:

I have removed all formatting, copied just the data to a new sheet (paste values), turned off auto calc, even rebuilt the wb from scratch. These things help for about a minute, but after a few iterations of the vba code (copies an "update" file to the "master" file), the problem starts all over again.

If I copy the data sheet (right-click on ws, copy/move to new wb) to a new wb, and save it as xlsx, even it is sluggish.

I can't decide if its THIS workbook, or Excel in general, or even network scans ongoing in the background. My other Excel workbooks (xlsx, xlsm) are functionally correctly.

When I look at system resources, physical memory is anywhere from 30-50% utilitzed, with nothing else running. I have 4GB of RAM, and am considering throwing in some more memory.

Right now, the fix seems to be copying just the data (highlighting everything from the A1 to the last used cell), copying to a new worksheet, and deleting the old.

This problem used to crop up about once or twice a month, but now it seems like it's happening every day. I run the VBA code several times a day.

Suggestions? Also, is there any benefit in saving as xlsb?

Thank you,

Karen

[snb]

What's the result of:


sub snb()
for each sh in sheets
msgbox sh.querytables.count
next
end sub

[klandreth]

0 for all 3

[snb]

What's the result of:


Sub snb()
For Each sh In sheets
msgbox sh.usedrange.address
Next
End Sub


and of

Sub snb()
MsgBox UBound(ThisWorkbook.LinkSources())
End Sub

[Aussiebear]

Hi Karen, Without seeing your code its a little hard to determine. We could stand here all day second guessing what might be the issue.