File Size Problem

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
I have a file from another user that is out of control. I took raw data out of his workbook, and pasted values into a new workbook (A1:AF35000). The size of the new file is 12 megb. Am I missing something or should the file be that size? Thanks for any suggestions offered.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
That is a awfully lot of data. Check for cell formatting "color/font ect.." this can all add alot of size to the file. Also try running these 2 macros, sometimes they work sometimes they dont but when they do oooooo the file size can shrink sometime 1000%

Rich (BB code):
Sub ExcelDiet()
     
    Dim x           As Integer
    Dim LastRow     As Long
    Dim LastCol     As Integer
     
    Application.ScreenUpdating = False
     
    On Error Resume Next
     
    For x = 1 To Sheets.Count
        Sheets(x).Activate
        LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete
        Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete
    Next x
     
    On Error GoTo 0
     
    Application.ScreenUpdating = True
     
End Sub


Sub WorkbookReducer()
    'From: http://www.contextures.com/xlfaqApp.html#Unused
    ' Re-set used range
    
    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim wks As Worksheet
    Dim dummyRng As Range
        
        For Each wks In ActiveWorkbook.Worksheets
            With wks
                myLastRow = 0
                myLastCol = 0
                Set dummyRng = .UsedRange
                On Error Resume Next
                myLastRow = _
                    .Cells.Find("*", After:=.Cells(1), _
                    LookIn:=xlFormulas, LookAt:=xlWhole, _
                    SearchDirection:=xlPrevious, _
                    SearchOrder:=xlByRows).Row
                    myLastCol = _
                    .Cells.Find("*", After:=.Cells(1), _
                    LookIn:=xlFormulas, LookAt:=xlWhole, _
                    SearchDirection:=xlPrevious, _
                    SearchOrder:=xlByColumns).Column
                On Error GoTo 0
                
                If myLastRow * myLastCol = 0 Then
                        .Columns.Delete
                    Else
                        .Range(.Cells(myLastRow + 1, 1), _
                        .Cells(.Rows.Count, 1)).EntireRow.Delete
                        .Range(.Cells(1, myLastCol + 1), _
                        .Cells(1, .Columns.Count)).EntireColumn.Delete
                End If
            End With
        Next wks

End Sub
 
Upvote 0
That sounds reasonable.... you have 35,000 rows * 32 columns = 1,120,000 cells. Depending on how many characters each cell stores, 12MB could make sense.
 
Upvote 0
Ran both, and the file is the same size. I did paste the values, so I do imagine that the formatting did not copy accross. Anyway I would appreciate if you have any other suggestions. Thanks again for your help.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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