Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reset button code for Radio Buttons | Excel Discussion (Misc queries) | |||
reset the range of FOR loop | Excel Discussion (Misc queries) | |||
using Debra Dalgleish toolbar code | Excel Discussion (Misc queries) | |||
x y scatter chart series ranges reset to x(range) = y(range) | Charts and Charting in Excel | |||
How do I reset the LastUsed cell reference from code. | Excel Programming |