#1
|
|||
|
|||
excel file size increasing incorrectly
i am running office 365 home OS win 10. i have a workbook with a nomnal size around 500Kb. Recently it has increased to around 10,000Kb for no apparent reason. I have ensured that there is nothing in cells outside the limits of the columns and rows in use. i can't attach due to file size. Any suggestions as to what I should look for? |
#2
|
|||
|
|||
Formatting in the blank rows and columns is the first thing
Check out this post https://www.msofficeforums.com/excel...-workbook.html can you upload to one drive or simular? |
#3
|
|||
|
|||
I had already seen that post and tried it out to no effect. my last attempt was to copy an empty unformatted cell and pasted (format only) to all cells outside the limits of my data. That doubled the file from 5,000Kb to 10,000.Kb.
i have 2 workbooks associated with the project. One is the main book used for data collection and processing. The other i use for code development. The development workbook has retained it's nominal 500Kb size while the main book keeps growing. I have the date of the onset of this problem (file history) but am unable to pinpoint what caused the change. I know I was working on code to insert new rows to the data around that time. I did ave some exciting momnts but that all occured in the development orkbook. i have used the ms file repair feature but that does not seem to fix the issue. |
#4
|
|||
|
|||
might just be corruppted, can you post it to onedrive or dropbox?
|
#5
|
|||
|
|||
excel file
The file is on Dropbox. i haven't shared from there before but i assume i'd need your email. what do you need from me. Although there is obviously something wrong the file is still functioning. i guess i can just make a copy and ensure my other backups are good. let me know what you need from me. i might not be able to do this today.
Mike C |
#6
|
|||
|
|||
when you select an item in drox boc you can click share > then create a link and post the link on here.
Make sure it is a copy though so we dont mess about with your proper file until you know what to do |
#7
|
|||
|
|||
excel file size increasing
|
#8
|
|||
|
|||
Running this macro against the file the size was reduced from 5,564 KB to 345 KB
The sheets didn't seem too bad except for the Emails sheet where the used range got reduced from last cell being M1048756 to H113. That's 13,633,828 cells down to 904, and there's still boarder formatting in column F running down to the end of the column. Code:
Sub LoseThatWeight() ' saved from post #4 of: ' https://www.mrexcel.com/forum/excel-questions/961348-how-get-rid-ghost-rows-without-saving-file.html Dim x As Long, Lastrow As Long, LastCol As Long Application.ScreenUpdating = False On Error Resume Next For x = 1 To Sheets.Count With Sheets(x) 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(Rows.Count, Columns.Count)).Delete .Range(.Cells(Lastrow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete End With Next x On Error GoTo 0 Application.ScreenUpdating = True End Sub |
#9
|
|||
|
|||
Yep, the macro is the best way of cleaning the file, but it was the 1m rows of formatting on the emails sheet from 113 to the bottom. Delete all that an you are down to 335.
Give it a try and let us know if it works. (if it does delete the file so no one else can download) |
Tags |
byte count, excel 365, xlsm |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word file with a lot of pictures. Picture size small, File size HUGE | Blackcloud_9 | Drawing and Graphics | 1 | 04-10-2018 06:04 AM |
Excel file size in OneNote 2016 | hirod | OneNote | 0 | 02-17-2018 01:09 AM |
Permanently increasing type size in Outlook | 417144 | Outlook | 2 | 05-01-2016 09:03 AM |
Permanently increasing type size in Outlook | 417144 | Outlook | 1 | 05-01-2016 12:41 AM |
Copying excel charts into word - huge file size. | Matt.M | Word | 2 | 04-02-2014 07:38 PM |