S
SteveDB1
Morning all.
Thanks to all of your respective helps, I have a great macro that performs
the reduction of unused rows and columns, across unhidden macros.
One individual showed me code that unhides, and then rehides worksheets.
However, when I insert the delete_unused_rows_Columns code into the
unhide-rehide code it no longer works.
The problem appears to be a method failed error. Here is the complete code.
----------------------------
Sub Print_Hidden_And_Visible_Worksheets()
'Dave Peterson
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Select 'here is where my error occurs.
'the error message is:
'run-time error 1004
'Method 'Select 'of object '_Worksheet' failed.
Application.ScreenUpdating = False
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
Next sht
'.PrintOut 'Ron says to change this to my code.
.Visible = CurVis
End With
Next sh
End Sub
-----------------------------
This is really odd, as I've tried the code Ron DB provided, and as a stand
alone it works exactly as prescribed. I've also tried the modified contextual
code for deleting the unused rows and columns, and it too works exactly as
prescribed. But as soon as I place the two together, I get the error.
As to the modifications that I've made.
I changed sh to sht because Ron's code for unhiding, and rehiding also uses
sh, and it called a duplication error, so I changed the variable name. I also
turned off sheet-updating" element so I don't use up unnecessary resources.
Please, show me what I'm missing here......
Thank you.
Thanks to all of your respective helps, I have a great macro that performs
the reduction of unused rows and columns, across unhidden macros.
One individual showed me code that unhides, and then rehides worksheets.
However, when I insert the delete_unused_rows_Columns code into the
unhide-rehide code it no longer works.
The problem appears to be a method failed error. Here is the complete code.
----------------------------
Sub Print_Hidden_And_Visible_Worksheets()
'Dave Peterson
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Select 'here is where my error occurs.
'the error message is:
'run-time error 1004
'Method 'Select 'of object '_Worksheet' failed.
Application.ScreenUpdating = False
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
Next sht
'.PrintOut 'Ron says to change this to my code.
.Visible = CurVis
End With
Next sh
End Sub
-----------------------------
This is really odd, as I've tried the code Ron DB provided, and as a stand
alone it works exactly as prescribed. I've also tried the modified contextual
code for deleting the unused rows and columns, and it too works exactly as
prescribed. But as soon as I place the two together, I get the error.
As to the modifications that I've made.
I changed sh to sht because Ron's code for unhiding, and rehiding also uses
sh, and it called a duplication error, so I changed the variable name. I also
turned off sheet-updating" element so I don't use up unnecessary resources.
Please, show me what I'm missing here......
Thank you.