Don't forget that when you delete the unused space, you must then save the
workbook. In some hard-core cases, also close workbook and quit Excel.
"Tempy" wrote in message
...
Hi All,

I am naming a rang in a spreadsheet with the following:
ActiveSheet.UsedRange.Name = "KTL"
This works fine, with the exception of that it see's about 5 empty
columns on the end of the spread sheet?
I also run the following to try and get rid of it, without success.

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), _
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
End Sub

Les Stout

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!