Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-09-2020, 07:29 AM
jmcsa3 jmcsa3 is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2019
Novice
excel file size increasing incorrectly
 
Join Date: Jan 2019
Location: UK
Posts: 14
jmcsa3 is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 09-09-2020, 07:58 AM
Purfleet Purfleet is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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?
Reply With Quote
  #3  
Old 09-09-2020, 08:53 AM
jmcsa3 jmcsa3 is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2019
Novice
excel file size increasing incorrectly
 
Join Date: Jan 2019
Location: UK
Posts: 14
jmcsa3 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 09-09-2020, 09:36 AM
Purfleet Purfleet is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

might just be corruppted, can you post it to onedrive or dropbox?
Reply With Quote
  #5  
Old 09-09-2020, 12:14 PM
jmcsa3 jmcsa3 is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2019
Novice
excel file size increasing incorrectly
 
Join Date: Jan 2019
Location: UK
Posts: 14
jmcsa3 is on a distinguished road
Default 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
Reply With Quote
  #6  
Old 09-09-2020, 12:59 PM
Purfleet Purfleet is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #7  
Old 09-09-2020, 02:25 PM
jmcsa3 jmcsa3 is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2019
Novice
excel file size increasing incorrectly
 
Join Date: Jan 2019
Location: UK
Posts: 14
jmcsa3 is on a distinguished road
Default excel file size increasing

Dropbox - DiddlyBIG.xlsm - Simplify your life


link to file as requested
Mike C
Reply With Quote
  #8  
Old 09-09-2020, 04:02 PM
NoSparks NoSparks is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #9  
Old 09-09-2020, 09:21 PM
Purfleet Purfleet is offline excel file size increasing incorrectly Windows 10 excel file size increasing incorrectly Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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)
Reply With Quote
Reply

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
excel file size increasing incorrectly Permanently increasing type size in Outlook 417144 Outlook 1 05-01-2016 12:41 AM
excel file size increasing incorrectly Copying excel charts into word - huge file size. Matt.M Word 2 04-02-2014 07:38 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:56 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft