I don't doubt what you are saying but that is truely odd. Just to confirm the
obvious... you do not have any code that might be creating the cells again
after the delete? You are actually deleteing the cells and not clearing the
contents? Here is code that I use to compact single sheets or entire
workbooks. You can give it a try if you wish...
Public Sub CompactAllSheets()
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In Worksheets
Call CompactSheet(wks)
Next wks
Application.ScreenUpdating = True
If MsgBox("For the compact to complete the spreadsheet must be saved. "
& _
"Do you want to save now?", vbYesNo + vbInformation, "Save?") = vbYes
Then wbk.Save
End Sub
Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range
If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete
End Sub
Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer
If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function
--
HTH...
Jim Thomlinson
Sandusky said:
I have deleted entire rows, saved, closed and reopened Excel - nada.
In my somewhat lengthy experience with Excel, deleting entire rows and
saving has always done the trick for me. No idea why it's not working now.
Dave Peterson said:
There was another step in those instructions. Did you do that, too?
Maybe it'll work for you.
When I said I selected all the rows below 300, I meant the entire rows,
and
I right-clicked and chose Delete. So yeah, I've tried that and it
doesn't
work.
http://www.contextures.com/xlfaqApp.html#Unused
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Excel 2003 SP3
Winows XP Pro SP2
I wrote some [bad] code that was "hiding" a value in cell A65536.
When I
realized this was causing tiny slider bars and also causing unusually
large file sizes, I removed the code. I also selected all the unused
rows (from about row 300) and deleted them, then saved. This still
didn't fix the tiny slider bar or file size problem - but it should
have,
no?
Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?
Any and all help appreciated. Thanks!
-gk-
