Reset Used Range, Debra Dalgliesh's code
Excel 2002, WinXP I ran Debra Dalgliesh's code to reset the used range on my file of some 12 sheets. I include her code below for reference. The code hung up on several of the sheets (not all) with the error "Cannot shift objects off sheet." The offending line of code was the command to delete the columns, near the end of the macro. I tracked down the individual sheets and the last row and last column. I tried to manually delete the columns and got the same error. I then selected the columns to delete and did (manually) Edit - Clear - All. I then ran the code again and all went well with that sheet. When the code hung up on another sheet, I inserted code to clear the range before deleting the columns. I used the "Clear" command because the macro recorder recorded "Clear" when I manually did Edit - Clear - All. That didn 't work for me. The code still hung up on the same sheet. I had to manually do Edit - Clear - All with each offending sheet over the range of columns to be deleted before the code would reset the used range. My "Clear - All" line of code and Debra's column-delete line of code is as follows: ..Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).Clear ..Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete Question: How can I code to clear everything, objects and all, in a range? Thanks for your help. Otto Sub ResetUsedRange() 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 Next wks End Sub |
Reset Used Range, Debra Dalgliesh's code
Any help in this article:
http://support.microsoft.com/default...69&Product=xlw "Cannot shift objects off sheet" error message when you hide columns in Excel -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel 2002, WinXP I ran Debra Dalgliesh's code to reset the used range on my file of some 12 sheets. I include her code below for reference. The code hung up on several of the sheets (not all) with the error "Cannot shift objects off sheet." The offending line of code was the command to delete the columns, near the end of the macro. I tracked down the individual sheets and the last row and last column. I tried to manually delete the columns and got the same error. I then selected the columns to delete and did (manually) Edit - Clear - All. I then ran the code again and all went well with that sheet. When the code hung up on another sheet, I inserted code to clear the range before deleting the columns. I used the "Clear" command because the macro recorder recorded "Clear" when I manually did Edit - Clear - All. That didn 't work for me. The code still hung up on the same sheet. I had to manually do Edit - Clear - All with each offending sheet over the range of columns to be deleted before the code would reset the used range. My "Clear - All" line of code and Debra's column-delete line of code is as follows: .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).Clear .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete Question: How can I code to clear everything, objects and all, in a range? Thanks for your help. Otto Sub ResetUsedRange() 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 Next wks End Sub |
Reset Used Range, Debra Dalgliesh's code
Thanks Tom. I took the little macro code that the article gave to change
the placement property of all the objects on the sheet, if any, and put the code into Debra's macro. Otto "Tom Ogilvy" wrote in message ... Any help in this article: http://support.microsoft.com/default...69&Product=xlw "Cannot shift objects off sheet" error message when you hide columns in Excel -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel 2002, WinXP I ran Debra Dalgliesh's code to reset the used range on my file of some 12 sheets. I include her code below for reference. The code hung up on several of the sheets (not all) with the error "Cannot shift objects off sheet." The offending line of code was the command to delete the columns, near the end of the macro. I tracked down the individual sheets and the last row and last column. I tried to manually delete the columns and got the same error. I then selected the columns to delete and did (manually) Edit - Clear - All. I then ran the code again and all went well with that sheet. When the code hung up on another sheet, I inserted code to clear the range before deleting the columns. I used the "Clear" command because the macro recorder recorded "Clear" when I manually did Edit - Clear - All. That didn 't work for me. The code still hung up on the same sheet. I had to manually do Edit - Clear - All with each offending sheet over the range of columns to be deleted before the code would reset the used range. My "Clear - All" line of code and Debra's column-delete line of code is as follows: .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).Clear .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete Question: How can I code to clear everything, objects and all, in a range? Thanks for your help. Otto Sub ResetUsedRange() 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 Next wks End Sub |
All times are GMT +1. The time now is 01:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com