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?
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?