PDA

View Full Version : Sleeper: Reducing file size



VickyB
09-02-2005, 10:48 PM
I have tried to implement your ExcelDiet code for reducing file size, as found at :
http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

It went beautifully for the first 8 of my 12 worksheets, then fell over when it went looking for shapes in the 9th sheet, which was 967 rows by 76 columns, with 6 blocks of data separated by up to 20 rows ( to allow for future expansion of data in each block).

The point of failure is the 2nd line in the code below "j="


For Each Shp In .Shapes
j = Shp.TopLeftCell.Row
k = Shp.TopLeftCell.Column
Do Until .Cells(j, k).Top > Shp.Top + Shp.Height
j = j + 1
Loop
If j > LastRow Then
LastRow = j
End If
Do Until .Cells(j, k).Left > Shp.Left + Shp.Width
k = k + 1
Loop
If k > LastCol Then
LastCol = k
End If
Next


Could anyone please give me some idea in debugging this problem. I have never come across shapes before in VBA. To the best of my knowledge, there are no shapes in this worksheet, just rows of data?

Justinlabenne
09-03-2005, 12:36 AM
I cannot replicate the issue here. Maybe you could attach a sample file of your workbook so it could be tested out and looked over.

Otherwise, I don't really know. I added shapes and mutliple split-up rows of formulas, etc.. but could not get an error.

VickyB
09-03-2005, 05:24 AM
Thanks for doing this, Justin. Can you please advise me if there is any code to determine if a shape is present in a worksheet? Is there any way of determining the type of shape, and the location of it in the workbook?

My last row number is 738. I have named Ranges in most of the columns in this worksheet that go down to row 1,000, with empty rows filling the gap. Could this cause a problem to the ExcelDiet code?

I would apprecaite any advice on this.

Many thanks,

VickyB

Jacob Hilderbrand
09-03-2005, 06:27 AM
Well, when we start the For Each loop, it should loop through all shapes and if there are no shapes it will not run the code at all. If you could attach the workbook, or just export the worksheet that is having trouble it would help.

Thanks

Jake

VickyB
09-03-2005, 02:43 PM
I am new to this site. How do I send the workbook or worksheet , please?

VickyB
09-03-2005, 03:18 PM
I worked out how to upload my sample workbook. Easy!!

Please find attached the workbook with the worksheet that causes ExcelDiet to crash. I did notice that in copying, Column AY containes more than 256 characters, and I manually copied that column so that the worksheet was the original.

I would appreciate any help with this.

Many thanks,
VickyB