Bogging down

[gambils]

I have one Excel file that is slow to open, often crashes Excel, etc. The only "complicated formula" (I have some "left" and "right" trim formulas) the worksheet has are v-lookups but I have limited the ranges searched- not searching entire columns. I have also saved the file in binary format. so only 2,765 KB.

I experimented yesterday and saved as a 97-2003 file and it opens and works like a dream but not sure if that's the best way to handle this.

I am using Excel 2016, have 20G of RAM, but on this one worksheet little actions like inserting a line give me the error message about not having enough Memory, to consider up-grading to 64-bit Excel, etc. But, I am afraid that might be over-kill, might give me compatibility issues (I use 32-bit at home and all my colleagues do as well.

I have 6 tabs with very small data on each that I search with the v-lookups- for instance if a PO posts this month, I might see if I have it on the
"validated tab" where in a prior month, I researched the PO so I don't need to re-create the wheel and research it again. But, again, per the first paragraph I am searching on a small defined range of cells.

Ideas?

 

[BarryL]

whats the last cell used on each sheet? as in go on each sheet and press control + End

Are they going far beyond where they should be?

 

[gambils]

On most of the sheets, the last one being used is in the very narrow range of where my data resides. On the PO search tab, however, even though in my v-lookup I am telling it to only look at a specified range, when I hit ctrl-end per your direction, it goes all the way to the bottom- row 1,048,576 So how do I rectify that?

 

[BarryL]

Yea its probably the cause of the slow down. Can you open the VBE (alt+F11) then press ctrl+G to open the Immediate Window. Then in the Immediate Window type the below and hit enter . MAKE SURE you have the troublesome sheet open as well

application.ActiveSheet.usedrange

Failing that (and sometimes it doesnt work for me) I would copy everything out of the workbook to a new workbook.

 

[Fluff]

Another option, if the above does not work.
Find the last used row on the sheet & select the row below > Ctrl + Shift + down arrow > right click > Delete.
Then select the first blank column > Ctrl + shift + right arrow > right click > delete.
Then save the file

 

[gambils]

Yes, I did that, too, as some of the sheets continued to show an end at the very bottom of the worksheet. But I also discovered there was something on the first tab in my formatting that Excel didn't like for some reason. I cleared all the formatting and copied the data back from the original version of the file and that seems to have made the difference. Wish I knew what was wrong with the formatting to avoid doing it again but, as it's working, I'll let sleeping dogs lie!