Excel 2007 - Cleaning Bloated Workbook 1

[ladyck3]

Is there a way to clean an Excel Workbook without having to reinvent the wheel?

My mind is thinking something like the "tracking changes" History or something.... the bloatware type thing that happens when you use the same file day in and day out and it just gets bigger each time you save it.

Let me explain that I have a report that I do every single day and it has 4 sets of filters I must apply to obtain the data needed. The RAW data, via 4 separate vlookups (4 worksheets) is used to filter from one to the next to the next. This file is now over 20MB and growing and every day it gets a little bigger.

I keep a lot of data in it but every day a previous day's worth of information is removed so the actual data in the file is not increasing. The bulk is a list of Service Orders for an entire month, it holds 30 days of data but its rolling data. So when I add the information for the 31st day, the data from the 1st is deleted.

I know the cells still keep unsceen data in them but I'm wondering if there is any way, short of recreating the entire venture time and time again... that I can just run some sort of "remove history" or something.

No I am not tracking changes on this workbook, heaven forbid, but that's what my mind keeps going back to, some sort of remove history function.

Please advise and feel free to ask questions in case I am not as clear as I think I am....

Thank you in advance.....


ladyck3
aka: Laurie

 

[MazeWorX]

you could try to reset the range used

Sub clrRng() 
    ActiveSheet.UsedRange 
    For Each Sh In ThisWorkbook.Worksheets 
        x = Sh.UsedRange.Rows.Count 
    Next Sh 
     
End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[SkipVought]

Phillips MOM, or magnesium citrate??? ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

How big (no. of rows) is the daily set of data?
Can you separate the raw data (access, excel) and use external database query with parameters to filter input data? Raw data could be regularly archived before removing old records if it takes place. One can remove query definition from the copy of the report file to break a link between data.


combo

 

[ladyck3]

I'm about to try the VB code. Will let you know if I figure that out? I'm assuming to make that a macro, well copy it into the editor and then assign... oh never mind LOL (Skip stop laughing at me... LOL) BTW Skip, MOM? More like some sort of insanity juice is what I need

Combo, that's far more advanced that I need it to be.... and I need it yesterday... I seriously don't have the time to figure all of that out right now It probably makes perfect sense but I'm in the middle of TOO MUCH to have any spare brain particles to expend on the method you discussed.

Be Back Later with results

ladyck3
aka: Laurie

 

[SkipVought]

As a practicing Septuagenarian, I often feel as if I’ve imbibed Milk of Amnesia.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Before now, I've sometimes found that Excel behaves as if it believes it has data below where the actual data ends (e.g. this would affect the size of the vertical slider bar at the right of a sheet). As far as I know however, when it checks "used range" it still gets it right. I don't know why, but deleting (not clearing) everything below the true used bottom row sometimes seems to sort this out.

Given the above, you could try running this macro when you've finished doing your updates.

Public Sub DeBloat()
Dim sh As Worksheet, rmax As Long
For Each sh In ThisWorkbook.Worksheets
    With sh
        rmax = .UsedRange.Row + .UsedRange.Rows.Count
        .Rows(Trim(Str(rmax)) & ":65536").Delete
    End With
Next sh
End Sub

At least, it should not do any harm.

Maybe when Skip stops giggling, he can tell you the neat way of writing the above.

BTW, I'm assuming your version of Excel has a max of 65536 rows. If not, the above will certainly need modding.

Tony

 

[SkipVought]

in order to make it applicable in multiple versions...

Public Sub DeBloat()
Dim sh As Worksheet, sRow As String, sCol as String, sMaxRows as String, sMaxCols as string

with ThisWorkbook.Sheets(1)
  sMaxRows = str(.rows.count)
  sMaxCols = str(.columns.count)
end with 

For Each sh In ThisWorkbook.Worksheets
    With sh
        sRow = str(.UsedRange.Row + .UsedRange.Rows.Count)
        sCol = str(.UsedRange.column + .UsedRange.columns.Count)

'[b]delete rows/column with no VALUES, [highlight]but may have DATA[/highlight][/b]

        .Rows(sRow & ":" & sMaxRows).Delete
        .Columns(sCol & ":" & sMaxCols).Delete

    End With
Next sh
End Sub

The other thing to look at is, do you have FORMULAS in rows or columns, in anticipation of data being entered some time in the future?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MazeWorX]

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[N1GHTEYES]

Skip:

'delete rows/column with no VALUES, but may have DATA

Can you explain this? I don't understand.

Tony

 

[SkipVought]

FORMATTING, for instance is DATA with no VALUE, in terms of property. You may also have some other OBJECT that has no VALUE property AND cannot be removed from the sheet using the [Delete] Key.

I have seen workbooks that have a large filesize that was significantly reduced by DELETING rows below the cell in the last row, with a VALUE and deleteing columns to the right the cell in the last column, with a VALUE. And that is because ther was DATA in those rows/columns that were DELETED.

Deleting in this manner is NOT JUST HITTING THE [Delete] KEY. That simply deletes VALUES, but does not delete ALL DATA. You must RIGHT-CLICK > DELETE, thus SHIFTING ROWS/COLUMNS.

BTW, I would use the CurrentRegion property, which returns the contiguous range that contains VALUES as opposed to the UsedRange property that returns a contiguous range containing DATA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

So, deleting below the UsedRange won't delete data, but also won't necessarily get rid of bloat, whereas CurrentRange is more likely to remove bloat but might delete data? Is that what you're saying Skip?

So, in answer to the OP's question, what code (if following this route) would you suggest?

BTW I tried a quick test. I bunged a bunch of random data into various random cells then ran the macro. That obviously did not do much but at least it proved the cells I had filled were not wiped.

Then I went wayyyy down the sheet and added more data to a cell. I noted at this point that the slide-bar on the right of the sheet had become a lot smaller. Obviously this is becasue Excel recognises that the sheet is bigger. I then deleted the entries in the lowest cell. The slide bar stayed the same size - as if Excel still thought the the sheet was quite long. Then I ran the macro. The slide bar went back to its original size. This seems to indicate that the macro causes Excel to reset it's opinion of the sheet size. That is why I thought the macro might help the OP

I still don't know what is really going on there, but I'd be curious to know if the OP found that it solved her problem.

Tony

 

[lionelhill]

Thanks for focussing attention on this, and presumably the related problem that some workbooks print empty pages after the end of visible data.

Skip, you are probably already aware, but for those who are not, the anaesthetic propofol is has been nick-named Milk of Amnesia because it looks milky (for chemists it's diisopropylphenol and presumably milky in suspension??). Fact for the day.

 

[N1GHTEYES]

Or 2,6-bis(propan-2-yl)phenol if you want to go all IUPAC.

 

[ladyck3]

^ ^ ^ ^
Officially Lost
LOL.. but then Skip, anyway, knew that of me. LOL

ladyck3
aka: Laurie

 

[N1GHTEYES]

Laurie: Sorry for the chemical digression. Did you try either my code, or Skip's improvements on it? Did it help?

Tony

 

[lionelhill]

Nighteyes, I am so sorry, I should have at least specified the positions round the phenol! You didn't include its CAS-number though?
Oh, and for completeness, I checked: it looks milky because it's supplied in a soybean oily emulsion. That probably means that if you get anaesthetised you can count it towards your 5 a day of vegetables?

 

[N1GHTEYES]

CAS? Now you've lost me. The last time I did any chemy was well over 30 years ago. This old memory is begining to fail...

Tony

 

[HPSoPete]

The easy way to get rid of this bloat issue is to simply save the spreadsheet with a new file name. The new file will have just your most recent data, no history.

 

[ladyck3]

Is that for real?

It just "might" be that simple because how I ultimately resolved the issue is to copy just the data from the 20MB file into a new file worksheet by worksheet and now its just 2.5MB and has not grown save for normail growth of the additon of new daily data.

I am gonna try that!

Thanks...HPSoPete
Laurie

Milk of Magnesia.... I"m still lost on all of that....
I guess you call it "Clear as MUD" LOL


ladyck3
aka: Laurie