Only one sheet very slow

N

nisanthp

New Member
Joined
Jul 1, 2009
Messages
26
Hi all,


Im facing a problem with one sheet in an xlsx file.

The file has 3 sheets 1 this sheet, one blank sheet for future data and another one with hadly 20 rows. The file size is 361 kb only.

The sheet contains almost 700 rows and column AT. Not all the columns are occuppied only labelled till AT. but all the columns are with filter. 3 columns are having a vlookup to next sheet. the sheet is responding in snail pace, not snail is better than this. this is happening with this sheet only,

wht cud be the reason. i searched this to check some similar disucssion was here but cudnt find in this forum.

pls help.

thanx in advance
 
Sort by date Sort by votes
dissecting out the code on this page that repeats all the way down

Code: 
    Range("G3").Value = "=(RC[1]&""/""&RC[2]&""/""&RC[3]&""/""&RC[4]&""/""&RC[5])"
    Range("Q3").Value = Range("P3") * Range("O3").Value
    Range("R3").Value = Range("N3").Value
    Range("T3").Value = Range("S3").Value * Range("O3").Value
    Range("U3").Value = Range("S3").Value * Range("Q3").Value
    Range("Z3").Value = Range("X3").Value * Range("Q3").Value
    'Range("AA3").Value = Range("X3") * Range("Y3").Value + 1 ' not useable
    Range("AA3").Value = Range("AA3").Value
    Range("AB3").Value = Range("O3").Value * Range("AA3").Value
    Range("AD3").Value = Range("O3").Value * Range("AC3").Value
    Range("AG3").Value = Range("AA3").Value * Range("AF3").Value
    Range("AJ3").Value = Range("AI3").Value * Range("AG3").Value
    Range("AK3").Value = Range("O3").Value * Range("AJ3").Value
    Range("AL3").Value = Range("AJ3").Value + Range("AG3").Value
    Range("AM3").Value = Range("O3").Value * Range("AL3").Value
    Range("AM3").Value = "=IF(LOOKUP(D3,'Mark Up Category'!$A$4:$A$27)=D3,LOOKUP(D3,'Mark Up Category'!$A$4:$A$27,'Mark Up Category'!$B$4:$B$27),NA())"
    'Range("AO3").Value = Range("AL3").Value + Range("AC3").Value
    'Range("AO3").Value = Range("AO3").Value
    'Range("AO3").Value = Range("AO3").Value * Range("AN3").Value
    Range("AP3").Value = Range("O3").Value * Range("AO3").Value
    Range("AQ3").Value = Range("AM3").Value + Range("AD3").Value + Range("AP3").Value
    Range("AQ3").Value = Range("AQ3").Value
    Range("AQ3").Value = Range("AQ3").Value / Range("O3").Value
    Range("AR3").Value = Range("O3").Value * Range("AQ3").Value

I was looking to see what could be picked up in VBA and paste the value rather than recalculate

O is a strange value line 3 as "=1.3-0.5"

maybe this will trigger some thoughts
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
The reason of issue is in 57512 empty (invisible without selection) rectangular autoshapes in sheet BOQ.
You can find first rectangular over the cell M328

Deleting of those autoshapes will solve the issue.
But in Excel 2007 it’s too long, seems that it never ends because Microsoft Office Program recovery process is activated for rebuilding workbook at each shape deleting.

The faster way:
1. Rename "Test file.xlsx" to "Test file.zip"
2. Open archive "Test file.zip" in explorer, delete the folder XL\DRAWINGS in it and save changes of archive
3. Rename back "Test file.zip" to "Test file.xlsx"
4. Open "Test file.xlsx" in Excel 2007, ignore warning message about autoshapes deleting, save workbook. Now it’s fast.

Alternatively you can save workbook in XLS format and apply such VBA code to delete autoshapes:
Rich (BB code): 

Sub ShapesDelete()
  Sheets("BOQ").Rectangles.Delete
End Sub
After that you can save the cleaned XLS as XLSX

Regards
 
Upvote 0
ZVI said:
The reason of issue is in 57512 empty (invisible without selection) rectangular autoshapes in sheet BOQ.
You can find first rectangular over the cell M328

Deleting of those autoshapes will solve the issue.
But in Excel 2007 it’s too long, seems that it never ends because Microsoft Office Program recovery process is activated for rebuilding workbook at each shape deleting.

The faster way:
1. Rename "Test file.xlsx" to "Test file.zip"
2. Open archive "Test file.zip" in explorer, delete the folder XL\DRAWINGS in it and save changes of archive
3. Rename back "Test file.zip" to "Test file.xlsx"
4. Open "Test file.xlsx" in Excel 2007, ignore warning message about autoshapes deleting, save workbook. Now it’s fast.

Alternatively you can save workbook in XLS format and apply such VBA code to delete autoshapes:
Rich (BB code): 

Sub ShapesDelete()
  Sheets("BOQ").Rectangles.Delete
End Sub
After that you can save the cleaned XLS as XLSX

Regards
Click to expand...

nice catch, what cause those to develop, for my own and others reference
 
Upvote 0
Hi Zvi.......


thaaaaannnnxxxxx a zillion....... it worked

you saved my 5 days effort. i had almost given up tht file and was preparing to start from scratch...

once again thanx for all those offered help
 
Upvote 0
I'm glad it helped!
And thanks for the riddle type I like to solve of :)
 
Upvote 0
Small tip: If you would have opened the selection pane (home tab, binoculars button on the far right), then you'd have spottd all those shapes yourself...
 
Upvote 0
I too have suffered from this and cannot figure out how I got "infested".

I have to include a "Shape.Delete" routine to keep them at bay.

Does anyone know how they "creep" in?

Cheers,
Scott
 
Upvote 0
Brilliant! Thank you, your post saved me a lot of time! I had 600x32 cells with CF, and copying and pasting (with a macro) just appended more CF every time. in the end I just pasted values and this has solved my problem.

jkpieterse said:
I can think of a number of things:

- many objects on the sheet (possibly hidden or of zero width)
- A lot of Conditional formatting.

Excel 2007 has a bug where copying a range with a CF on top of another range with also a CF will add the copied CF on top of the existing one, rather than replacing the CF. If your sheet undergoes a lot of copying and pasting and has CF applied, this may cause lots and lots of CF rules in your sheet.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
VBA to copy multiple worksheets and either rename sheet with file name or add column with filename
Replies
5
Views
2K
I_know_nuffin
I
H
  • Question Question
Update One Sheet To Another
Replies
0
Views
230
honkin
H
AndyTampa
  • Question Question
I'm looking for suggestions how to save changes of one sheet only.
2
Replies
13
Views
946
TryingToLearn
TryingToLearn
A
  • Question Question
First VBA Project - is there a better way to write this code?
Replies
0
Views
2K
akshay27
A
Jyggalag
  • Question Question
Macro to conditionally insert data from one sheet into another
Replies
1
Views
877
Jyggalag
Jyggalag

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top