h90 Posted November 4, 2005 Share Posted November 4, 2005 I have an Excel file with growing size. I read before that it is possible to delete unnecessary dates, like history which is safed along with the file. I heared it is possible to reduce to the half filesize and there are tools for that. I googled for that but did not find anything usefull. Anyone here has an idea? Link to comment Share on other sites More sharing options...
Crossy Posted November 4, 2005 Share Posted November 4, 2005 Unfotunately Excel does not have a 'compact' function like Access does Usually the cause of file bloat is that Excel thinks the data is bigger than it really is. Try this on a COPY of your file, just in case disaster strikes. You know the last column of your data and the last row. The cell at the bottom right of this range is the last cell of your actual data. Now do Ctrl - End That takes you to the cell that Excel thinks is the last cell of your data. If they differ significantly do this:- In the Name box of your sheet (usually right above column A) type X:65536 where X is the row number immediately below the last row of your actual data. Hit Enter. This will select every cell below your last data. Do Edit - Clear - All. This clears every row below your actual data. In the same Name box type X:IV where X is the column letter of the column immediately to the right of the last column of your actual data. Hit Enter. This will select every cell to the right of your last data. Do Edit - Clear - All. This clears every column to the right of your actual data. Save the file. Close the file. Open the file. Repeat for each worksheet. Worth a shot, as always YMMV. Link to comment Share on other sites More sharing options...
WhiteShiva Posted November 4, 2005 Share Posted November 4, 2005 Have you tried simply saving the file under a different name? This usually works with Adobe files when they get too large (after editing). Worth a shot. perhaps? Link to comment Share on other sites More sharing options...
h90 Posted November 4, 2005 Author Share Posted November 4, 2005 I'll try that! Thanks a lot Unfotunately Excel does not have a 'compact' function like Access does Usually the cause of file bloat is that Excel thinks the data is bigger than it really is. Try this on a COPY of your file, just in case disaster strikes. You know the last column of your data and the last row. The cell at the bottom right of this range is the last cell of your actual data. Now do Ctrl - End That takes you to the cell that Excel thinks is the last cell of your data. If they differ significantly do this:- In the Name box of your sheet (usually right above column A) type X:65536 where X is the row number immediately below the last row of your actual data. Hit Enter. This will select every cell below your last data. Do Edit - Clear - All. This clears every row below your actual data. In the same Name box type X:IV where X is the column letter of the column immediately to the right of the last column of your actual data. Hit Enter. This will select every cell to the right of your last data. Do Edit - Clear - All. This clears every column to the right of your actual data. Save the file. Close the file. Open the file. Repeat for each worksheet. Worth a shot, as always YMMV. <{POST_SNAPBACK}> Link to comment Share on other sites More sharing options...
h90 Posted November 4, 2005 Author Share Posted November 4, 2005 strange it made the filesize from 8.24 MB to 13.2 MB..... so next idea, please hahahaha.... Unfotunately Excel does not have a 'compact' function like Access does Usually the cause of file bloat is that Excel thinks the data is bigger than it really is. Try this on a COPY of your file, just in case disaster strikes. You know the last column of your data and the last row. The cell at the bottom right of this range is the last cell of your actual data. Now do Ctrl - End That takes you to the cell that Excel thinks is the last cell of your data. If they differ significantly do this:- In the Name box of your sheet (usually right above column A) type X:65536 where X is the row number immediately below the last row of your actual data. Hit Enter. This will select every cell below your last data. Do Edit - Clear - All. This clears every row below your actual data. In the same Name box type X:IV where X is the column letter of the column immediately to the right of the last column of your actual data. Hit Enter. This will select every cell to the right of your last data. Do Edit - Clear - All. This clears every column to the right of your actual data. Save the file. Close the file. Open the file. Repeat for each worksheet. Worth a shot, as always YMMV. <{POST_SNAPBACK}> Link to comment Share on other sites More sharing options...
malcolminthemiddle Posted November 4, 2005 Share Posted November 4, 2005 Turn Off functions like "Auto Data" when you are not using them. Link to comment Share on other sites More sharing options...
Crossy Posted November 4, 2005 Share Posted November 4, 2005 strange it made the filesize from 8.24 MB to 13.2 MB..... so next idea, please hahahaha.... Oops, at least it changed the filesize Link to comment Share on other sites More sharing options...
Youbloodybeauty Posted November 4, 2005 Share Posted November 4, 2005 Try this: Sub ReduceFilesize() ' 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 Link to comment Share on other sites More sharing options...
Youbloodybeauty Posted November 4, 2005 Share Posted November 4, 2005 I found the above macro somewhere and I tried it but seems to be an error in it. If it doesn't work have a look here: http://www.mrexcel.com/board2/viewtopic.ph...highlight=bloat Make sure you run the macro on a COPY of your original in case there are any problems. YBB Link to comment Share on other sites More sharing options...
Youbloodybeauty Posted November 5, 2005 Share Posted November 5, 2005 I found the above macro somewhere and I tried it but seems to be an error in it. The error was because of a conflict with another macro I have. Should be fine. Running it on a spreadsheet 1.2 meg reduced it to 900k. Great! YBB Link to comment Share on other sites More sharing options...
sting01 Posted November 5, 2005 Share Posted November 5, 2005 Open your sheet, then on the top bar menu : File>save as select tab delimited, or CSV, or text (depend of the versin of excel) give a new name (in case of ) save. Close all Reboot unplug the computer, go to your favorite bar and take a beer (joking) After reboot, open excell then import data (or just drag and drop the text file you have created in Excell) you should have a new spreadsheet will only your data (Assuming you are looking only for that). Another way is to convert it in Acees format, then clean it with Access, and then save the cleaned speadsheet from Access as a Excell file (in that case you will keep the macro) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now