Excel Filesize

[h90]

I have an Excel file with growing size. I read before that it is possible to delete unnecessary dates, like history which is safed along with the file. I heared it is possible to reduce to the half filesize and there are tools for that.

I googled for that but did not find anything usefull. Anyone here has an idea?

[Crossy]

Unfotunately Excel does not have a 'compact' function like Access does

Usually the cause of file bloat is that Excel thinks the data is bigger than

it really is.

Try this on a COPY of your file, just in case disaster strikes.

You know the last column of your data and the last row. The cell at the

bottom right of this range is the last cell of your actual data.

Now do Ctrl - End

That takes you to the cell that Excel thinks is the last cell of your data. If they differ significantly do this:-

In the Name box of your sheet (usually right above column A) type X:65536

where X is the row number immediately below the last row of your actual

data.

Hit Enter. This will select every cell below your last data.

Do Edit - Clear - All. This clears every row below your actual data.

In the same Name box type X:IV where X is the column letter of the column

immediately to the right of the last column of your actual data.

Hit Enter. This will select every cell to the right of your last data.

Do Edit - Clear - All. This clears every column to the right of your actual data.

Save the file.

Close the file.

Open the file.

Repeat for each worksheet.

Worth a shot, as always YMMV.

[WhiteShiva]

Have you tried simply saving the file under a different name? This usually works with Adobe files when they get too large (after editing). Worth a shot. perhaps?

[h90]

I'll try that!

Thanks a lot

Unfotunately Excel does not have a 'compact' function like Access does 

Usually the cause of file bloat is that Excel thinks the data is bigger than

it really is.

Try this on a COPY of your file, just in case disaster strikes.

You know the last column of your data and the last row. The cell at the

bottom right of this range is the last cell of your actual data.

Now do Ctrl - End

That takes you to the cell that Excel thinks is the last cell of your data. If they differ significantly do this:-

In the Name box of your sheet (usually right above column A) type X:65536

where X is the row number immediately below the last row of your actual

data.

Hit Enter. This will select every cell below your last data.

Do Edit - Clear - All. This clears every row below your actual data.

In the same Name box type X:IV where X is the column letter of the column

immediately to the right of the last column of your actual data.

Hit Enter. This will select every cell to the right of your last data.

Do Edit - Clear - All. This clears every column to the right of your actual data.

Save the file.

Close the file.

Open the file.

Repeat for each worksheet.

Worth a shot, as always YMMV.

<{POST_SNAPBACK}>

[h90]

strange it made the filesize from 8.24 MB to 13.2 MB.....

so next idea, please hahahaha....

Unfotunately Excel does not have a 'compact' function like Access does 

Usually the cause of file bloat is that Excel thinks the data is bigger than

it really is.

Try this on a COPY of your file, just in case disaster strikes.

You know the last column of your data and the last row. The cell at the

bottom right of this range is the last cell of your actual data.

Now do Ctrl - End

That takes you to the cell that Excel thinks is the last cell of your data. If they differ significantly do this:-

In the Name box of your sheet (usually right above column A) type X:65536

where X is the row number immediately below the last row of your actual

data.

Hit Enter. This will select every cell below your last data.

Do Edit - Clear - All. This clears every row below your actual data.

In the same Name box type X:IV where X is the column letter of the column

immediately to the right of the last column of your actual data.

Hit Enter. This will select every cell to the right of your last data.

Do Edit - Clear - All. This clears every column to the right of your actual data.

Save the file.

Close the file.

Open the file.

Repeat for each worksheet.

Worth a shot, as always YMMV.

<{POST_SNAPBACK}>

[malcolminthemiddle]

Turn Off functions like "Auto Data" when you are not using them.

[Crossy]

strange it made the filesize from 8.24 MB to 13.2 MB..... 

so next idea, please hahahaha....

Oops, at least it changed the filesize

[Youbloodybeauty]

Try this:

Sub ReduceFilesize()

' Re-set used range

Dim myLastRow As Long

Dim myLastCol As Long

Dim wks As Worksheet

Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets

With wks

myLastRow = 0

myLastCol = 0

Set dummyRng = .UsedRange

On Error Resume Next

myLastRow = _

.Cells.Find("*", after:=.Cells(1), _

LookIn:=xlFormulas, lookat:=xlWhole, _

searchdirection:=xlPrevious, _

searchorder:=xlByRows).Row

myLastCol = _

.Cells.Find("*", after:=.Cells(1), _

LookIn:=xlFormulas, lookat:=xlWhole, _

searchdirection:=xlPrevious, _

searchorder:=xlByColumns).Column

On Error GoTo 0

If myLastRow * myLastCol = 0 Then

.Columns.Delete

Else

.Range(.Cells(myLastRow + 1, 1), _

.Cells(.Rows.Count, 1)).EntireRow.Delete

.Range(.Cells(1, myLastCol + 1), _

.Cells(1, .Columns.Count)).EntireColumn.Delete

End If

End With

Next wks

End Sub

[Youbloodybeauty]

I found the above macro somewhere and I tried it but seems to be an error in it.

If it doesn't work have a look here:

http://www.mrexcel.com/board2/viewtopic.ph...highlight=bloat

Make sure you run the macro on a COPY of your original in case there are any problems.

YBB

[Youbloodybeauty]

I found the above macro somewhere and I tried it but seems to be an error in it.

The error was because of a conflict with another macro I have. Should be fine.

Running it on a spreadsheet 1.2 meg reduced it to 900k. Great!

YBB

[sting01]

Open your sheet,

then on the top bar menu :

File>save as

select tab delimited, or CSV, or text (depend of the versin of excel)

give a new name (in case of )

save.

Close all

Reboot

unplug the computer, go to your favorite bar and take a beer (joking)

After reboot, open excell then import data (or just drag and drop the text file you have created in Excell)

you should have a new spreadsheet will only your data (Assuming you are looking only for that).

Another way is to convert it in Acees format, then clean it with Access, and then save the cleaned speadsheet from Access as a Excell file (in that case you will keep the macro)