I have an Excel file with UsedRange = A1:CH102. The saved size is 4151 kB. I have found a way to reduce the size:
Option Base 1
Sub ReduceSize()
Dim lAntR As Long
Dim iAntK As Integer
Dim aR() As Single
Dim aK() As Single
Dim n As Integer
Dim sFil1 As String
Dim sFil2 As String
Dim sKat As String
Dim sArk As String
sFil1 = ActiveWorkbook.Name
sKat = ActiveWorkbook.Path
sArk = ActiveSheet.Name
lAntR = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPreviou
iAntK = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPreviou
ReDim aR(lAntR)
ReDim aK(iAntK)
For n = 1 To lAntR
aR(n) = Rows(n).RowHeight
Next n
For n = 1 To iAntK
aK(n) = Columns(n).ColumnWidth
Next n
Application.CutCopyMode = False
Range(Cells(1, 1), Cells(lAntR, iAntK)).Copy
Workbooks.Add
sFil2 = ActiveWorkbook.Name
ActiveSheet.Name = sArk
ActiveSheet.Paste
Application.CutCopyMode = False
For n = 1 To lAntR
Rows(n).RowHeight = aR(n)
Next n
For n = 1 To iAntK
Columns(n).ColumnWidth = aK(n)
Next n
Workbooks(sFil1).Close savechanges:=False
Application.DisplayAlerts = False
Workbooks(sFil2).SaveAs sKat & "\" & sFil1
Application.DisplayAlerts = True
End Sub
But still the size is about 1400 kB.
Do anybody know what was occupying space outside the UsedRange?
Do anybody know what kind of format or content or anything else which takes much space? Is there a way to analyse the size of each cell or element in the sheet?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi MalicUK
There are many formulas in my sheet, so maybe I cannot reduce the size more than that. But I often find that the UsedRange reaches farther down and to the right than the range that really has content. Then it is no help to delete the actual columns or rows. The only way I have found to get a cleaner sheet is to copy the content to a new sheet, as I showed in the code. But it is a risk to lose some information, e.g. my code loses the print area.
smeden,
You don't need to use that to copy it to a new sheet. It is a well known bug in Excel - when you delete data is doesn't change the page dimensions and remember the cells are empty. All you have to do is highlight the first empty column and hold down Shift+Ctrl and press the right arrow. Now goto Edit->Delete. Now highlight the first empty row, hold down Ctrl+Shift and press the down arrow. Now Edit->Delete again. Now press Ctrl+Home. Now save your workbook. This should clear the sheets, and you wil have kept your print area.
And have you run that sub - what did the message box give as the total char length. You can add (a wild ass guess) 25% onto that for formatting and hopefully you should be near the file size.
Smeden,
This is a known issue, especially with Excel 97.
Does this happen when you have deleted some data off of a sheet (i.e. performed a "delete" command)?
It has been my experience that Excel doesn't know how to "release" its disk allocation when data has been deleted.
When you copy and paste to a new workbook, Excel tells the OS to start a fresh allocation for the new file, and obviously the OS can "forget" the sheet that was deleted.
This is only a theory of mine, but it makes sense given some of the other issues with Microsoft products.
Another example is Access, which has a "container" issue that becomes corrupt over time. I've also noticed that if I create and delete tables--even though my overall database hasn't changed in size--the entire file grows and grows and GROWS as I keep deleting and replacing the SAME data!
Hope that helps,
DrewK
Hi smeden,
If your scrollbar goes way past your data (lots of scrollbar travel left, even though you are at the bottom of the data), then you need to clear the blank rows and columns beyond the range of your data.
Go to the bottom row of data, then select the entire next row by clicking on the row number. Then CTRL + Shift + Down Arrow to select all the remaining rows in the worksheet. Use the Edit...Clear menu item to clear the rows of values and formats.
Next, go to the rightmost column of data, then select the entire next column by clicking on the column letter. Then CTRL + Shift + Right Arrow to select all the remaining columns in the worksheet. Use the Edit...Clear menu item to clear the columns of values and formats. As an option, you may want to select cell A1 (CTRL + Home in Excel 2002 + earlier; Home in Excel 2003).
Repeat the above procedure for each worksheet that has problems with the scrollbar extending past the data. Then Save the workbook. Close the workbook. Reopen the workbook, and the scrollbars should now extend only as far as the data.
In VBA, you can reset the scrollbar without saving, closing and reopening (once the unused range has been cleared) by executing a statement that uses ActiveSheet.UsedRange.Rows
Macro to automate the previous process:
Sub ExcelDiet()
Dim ws As Worksheet
Dim LastRow As Long, LastCol As Long
Application.ScreenUpdating
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
With ws
LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPreviou
LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPreviou
Range(.Cells(1, LastCol + 1), .Cells(65536, 256)).Delete
Range(.Cells(LastRow + 1, 1), .Cells(65536, 256)).Delete
LastRow = .UsedRange.Rows.Count
End With
Next ws
On Error GoTo 0
Application.ScreenUpdating
End Sub
Cheers!
Brad
smeden,
This is something i usually do.
I save the file in an earlier version of excel.
That's how u do it:
1. Click on file > Save as > Save as type > 95/5.0 excel document
This will reduce the size of file sustantially a decrease of 25% is no big deal.
WARNING! * You must take care that there should be not type of special formatting. If its normal data entry job its not going to create troubles but if it is something which has lotsa flashes n decorations it might have half features installed.
Hope the problem is solved.
Cheers!
Tapan Khatri.
In addition to what already been said I make it simple and easy for me by moving all data etc into a new workbook.
One other approach is to replace any eventuall array-formulas (both built-in and owned created) with simplet formulas although they require more space in the sheet(s).
Anyway, Charles William's excellent tool, FastExcel, is one tool that can analyze worksheets and workbooks and who also have some good information about memory & Excel:
http://www.decisionmodels.
Kind regards,
Dennis
Hi folks,
thank you all. Now I have tried all your suggestions. But still I am not satisfied. I have two workbooks which are quite similar, as they are based on the same workbook. Each of them contains 1 sheet with about 2600 filled cells. 245 cells contains formulas. Formatting of cells are quite similar.
No cells have text >255 characters.
File1 File2
Size (Excel2000) 4166 kb 1384 kb
Size (Excel5.0/95) 3587 kb 1196 kb
All text count 19780 31920
This means that there must be other diffenences. It should be possible to reduce the size of File1 much.
Do you have more suggestions?
Smeden,
First of all, You have managed to reduce the filesize with around 75 %. The pareto-statement says 80/20 so from my point of view it's obvious that to get the 20% the effort will probably not be proportional to the remaining percent.
What kind of formulas are in use in the 245 cells?
Is it possible for You to upload the samplefiles somewhere?
Kind regards,
Dennis
Dennis,
I have NOT managed to reduce the filesize of File1 to the size of File2. I think this might be possible, as the two files are so similar. I ask for a reason why File1 is so much larger than File2, and a method to reduce the filesize.
The formulas are all of a kind called DHENT() in Norwegian. I haven't found the English syntax, but the description is: Extracts a single value which fulfill the conditions you indicate, from a column in a list or database.
I do not know how to upload the files.
Check the Row Height for the last row in your UsedRange.
This worked for me, although it is probably not your problem, since you seem to have got to the bottom of your UsedRange issues, but other readers of this thread may benefit!
In my case I had a worksheet with a UsedRange extending to row 65536 for no apparent reason. The workbook was huge, and the scrollbars pretty useless. I tried all of the usual suspects (such as discussed above) to no avail. Whatever I did I could not reset the UsedRange sensibly.
In the end I discovered that the Row Height is not reset when you do Edit > Clear > Formatting (or > All), and this can cause the UsedRange to extend to the last row that has a non-standard Row Height. So, as well as clearing all data and formatting from your unused cells, ensure that they do not have a non-standard row-height setting.
In my case, this reduced the size of my workbook from 5.5M to 680K, and I only discovered it by saving the file as XML and inspecting the XML - you may find some clues to your problem by doing this.
AndyB
Hi MalicUK,
> ...This will remove everything.
Well, I also thought it would, but in my test case with Excel 2003 SP1, it does not.
My test case has a single sheet containing no data, no formatting, no defined names, no querytables or anything that I can detect. If I do as you suggest and ...
select 65536 entire rows,
delete them all
select $A$1,
execute "?ActiveSheet.UsedRange.Ro
save the workbook,
exit Excel and reopen the workbook
...
My UsedRange still has 65536 rows.
If I now select all rows and adjust the row height (so they are all the same height), then select A1 again, my UsedRange (using VBE) suddenly has one row, and on saving the file size reduces from 1.5MB to 15KB.
PS: My test case was extracted from a complex workbook I was trying to fix, not created from scratch, and I now find that I can't reproduce it from scratch. Having spent *many* hours trying to get to the bottom of this, I'm prepared to accept that I've missed something, but I hope this tip may help another reader.
Regards
AndyB
I came here looking for ways to reduced Excel file size and wanted to offer something I found.
An Excel file can grow substantially (factor of 2) when a chart's location is changed from being an object in a sheet to being a separate sheet.
In particular if the chart is an object on the same sheet where the source data is located, the file size will be minimal. In the case where the source data comes from multiple sheets, the optimum size seems to occur when the chart is on the sheet with the largest source data range. This suggests that there is a penalty for linking data between sheets.
Marty
Business Accounts
Answer for Membership
by: MalicUKPosted on 2004-12-09 at 07:01:34ID: 12783819
Hi smeden,
What is in those cells. Are there many formula? If so then it -could- be that. If there are no formula and the cells just contain data, then how much data is there. The following sub will tell you the total length of all the text in your sheet. Then you need to think about the formatting and pictures etc. does it come close?
Sub CountAllText()
Dim Cel As Range
Dim ws As Worksheet
Dim result As Integer
For Each ws In ActiveWorkbook.Worksheets
For Each Cel In ws.UsedRange
result = result + Len(Cel.Formula)
Next
Next
msgbox result
End Sub
Cheers,
MalicUK.