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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
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! --- Regards, Norman "Tempy" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
Hi Tempy,
I forgot to add: And what do you see in the formula bar when you select X100. --- Regards, Norman "Norman Jones" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
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. --- Regards, Norman "Tempy" wrote in message ... 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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range - Empty columns
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! |
Reply |
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) |