Named Range - Empty columns

[Tempy]

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!

 

[Norman Jones]

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.

 

[Tempy]

Hi Norman,

unfortunately that was a typo, i do have next wks at the bottom.
These columns do not have anything in at all and that is the main
problem?

Les Stout

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

 

[Norman Jones]

Hi Tempy,

Try hitting Ctl End (both keys) and (presumably) the cursor jumps to a cell
in the last of the 'empty' five columns.
Assuming the cell is X100. In cell X101 enter the formula
=len (X100)
What do you now see in cell X101 - obviously, replace the cell references
with your own!

 

[Norman Jones]

Hi Tempy,

I forgot to add:

And what do you see in the formula bar when you select X100.

 

[Tempy]

Hi Norman,

In cell x101 is 0 and in formula bar with x100 selected blank

Les Stout

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

 

[Mike Fogleman]

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.

 

[Norman Jones]

Hi Tempy,

I could reproduce your situation if I added a cell outside the existing
used range, formatted the cell and then deleted a cell (or cleared its
contents) .

I found that adding the line:

Set dummyRng = .UsedRange

immediately before the End With line at the foot of the macro obviated the
problem.

 

[Tempy]

Thanks for the help Norman, but my problem is still there?

The spread sheet is created automatically in Germany from various main
frame sysytems and is very large, 5000 lines and up to column EV

Les Stout

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