Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The XLS file is huge!
Hi Niklas
Excel can think that the usedrange is bigger then it is. You can see how big your usedrange is on every sheet with CTRL-END If the row/column is not the last row/column with data then: 1 Select the first row below your last row with data 2 CTRL-SHIFT-DOWN ARROW 3 Right click on the selection and choose delete And then for the columns 1 Select the first column next to your last column with data 2 CTRL-SHIFT-RIGHT ARROW 3 Right click on the selection and choose delete Save the file an close it Don't save the file in two versions but only as Microsoft excel workbook Not as 97-2002 & 5.0/95 workbook(this make your workbook big) You can use Rob Bovey's Code cleaner to clean up your code http://www.appspro.com/utilities/utilities.asp Or use a Macrofrom Debra Dalgleish her site Sub DeleteUnused() Dim myLastRow As Long Dim myLastCol As Long Dim wks As Worksheet Dim dummyRng As Range For Each wks In ActiveWorkbook.Worksheets With wks 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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Niklas" wrote in message ... Hi My XLS file consists of 20 sheets with between 10 and 150 rows and no more than 12 columns. On the sheets their are some buttons, ckeckboxes and comboboxes. This file is 12 MB and it is to big. Are their any "Tips and tricks" to make it smaller? Are their any compress/shrink file methods like in Access? Regards /Niklas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Huge File. | Excel Discussion (Misc queries) | |||
i need to import a huge txt file into excel | Excel Discussion (Misc queries) | |||
Huge file import is truncated | Excel Discussion (Misc queries) | |||
huge huge excel file... why? | Excel Discussion (Misc queries) | |||
File got huge | Excel Discussion (Misc queries) |