Hi Tempy,

Firstly, in transcribing Debra Dalgleish's DeleteUnused routine, you have
dropped the necessary penultimate line:

Next wks

If this is merely a typo in your post, it is posiible that one (or both) of
problem columns contain
cell(s) with hidden data - perhaps spaces or formulae which evaluate to "".
Another possibility is that you have data in these columns which is hidden
with a white font colour.

If you are SURE that you do not need any data in the five offending columns,
select the columns, hit your delete key and then re-run the sub.


---
Regards,
Norman


"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!