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

 

[SkipVought]

Cleaning Bloated Workbook

Solution: MOM! ;-)

Skip,

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

 

[ladyck3]

OMG... <donning dunce cap>

Hey... I hadda blonde senior moment shup!! LOL

ladyck3
aka: Laurie

 

[bdjb]

Hi Skip,
I get an error "Run-Time error '13" Type Mismatch" on your code. I'm running Office 2003. Any ideas?

Thanks

Bob

 

[SkipVought]

on what statement, please?

Skip,

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

 

[bdjb]

Sorry, On

.Rows(sRow & ":" & sMaxRows).Delete

 

[SkipVought]

interesting. It seems that the Rows and Columns properties behave differently...

Public Sub DeBloat()
Dim sh As Worksheet, sRow As String, lCol As Long, sMaxRows As String, lMaxCols As Long

With ThisWorkbook.Sheets(1)
  sMaxRows = Trim(Str(.Rows.Count))
  lMaxCols = .Columns.Count
End With

For Each sh In ThisWorkbook.Worksheets
    With sh
        sRow = Trim(Str(.UsedRange.Row + .UsedRange.Rows.Count))
        lCol = .UsedRange.Column + .UsedRange.Columns.Count

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

        .Rows(sRow & ":" & sMaxRows).Delete
        .Range(.Columns(lCol), .Columns(lMaxCols)).EntireColumn.Delete

    End With
Next sh
End Sub

Skip,

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