Delete does NOT delete

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel 97, SR2
Using large spreadsheets.
Doing lots of cut & paste and deletes
Finished spreadsheet gets exported/merged with our main
operation data-base ( not d-base or anything like that )
Have found that a delete ( and cut & paste?) actually leaves
everything in the file (althought hidden to the eye). Afer some
manipulation of the file, the file becomes hugh! This messes
up our export/merge, big time.
Our internal "fix" has been to convert to CSV (comma seperated
values), and reformat to get the required appearance back.

1: Is there a way to properly clean up the file so that what is on
the screen, etc., is actually what is in the file?
2: Do the newer versions of Excel ( 2003? ) have a built in fix?
(for this specific problem? (As we see it))
 
John
What you delete or cut\paste, is gone. There is no hidden anything.
What is not gone is the used range and this may be what is making your file
so large.
For example, do the following. Open a new blank spreadsheet (file).
Put some text in A1. Now put some text in F20. Now select any cell. Now
do Ctrl-End. Excel jumps to F20. That means that Excel thinks that F20 is
the last cell of your used range and if you saved the file, the file would
be of a size commensurate with that much used range.
Now clear (delete) the contents of F20. Now you and I know that the
used range of your data is A1. But do Ctrl-End again and you will see that
F20 is what Excel thinks is the extent of your used range.
The range you used in this example is small and the difference in file
size is small. But think about a range that is several thousand times the
size you just used and you will understand that things can go south real
quick. Then you throw in numerous sheets on top of that.
What you need to do, if this is your problem, is to delete all the rows
and columns below and to the right of your actual used range. All the
deleted rows and columns will be replaced by Excel immediately but they will
be replaced with rows and columns that Excel does not consider to be in your
used range.

Here is the procedure if you want to do this manually. Say your actual last
cell is F10.
Select row 11, the whole row.
Do Ctrl-Shift-Down Arrow.
This selects all the rows from 11 down to the end of the sheet.
Do Edit-Delete
Now select Column G, the whole column.
Do Ctrl-Shift-Right arrow.
Do Edit-Delete.
Do this for each sheet in the file.
Save the file
Close the file
Open the file.

You can also do this with VBA. The code is as follows. This code is from
Debra Dalgliesh at:
http://www.contextures.on.ca/xlfaqApp.html#Unused


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
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _ 'The key in
all this is that the search starts with the
LookIn:=xlFormulas, lookat:=xlWhole, _ 'first cell (A1) and
the direction of search is
searchdirection:=xlPrevious, _ 'Previous.
This results in the search starting at
searchorder:=xlByRows).Row 'the last cell
and going backwards, first by rows
myLastCol = _ 'to
get the last row and then by columns to get the
.Cells.Find("*", after:=.Cells(1), _ 'last
column.
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

HTH Otto


(e-mail address removed)...
 
Back
Top