Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
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! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste to next empty cell in named range of cells | Excel Discussion (Misc queries) | |||
Using named range to extend print area for variable number of columns | Excel Worksheet Functions | |||
VBA: find number of columns in named range? | Excel Discussion (Misc queries) | |||
Number of Rows & Columns in a Named Range | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) |