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.

You error occures because you can not select a hidden worksheet. That being
said you do not have to unhide the worksheets to do your compatcting. Try
this code...

(remove the commenting to toggle the visible setting if you want to)

Sub CompactAllSheets()
Dim wks As Worksheet
'Dim lngVisible As Long

For Each wks In Worksheets
'lngVisible = wks.Visible
CompactSheet wks
'wks.Visible = lngVisible
Next wks

End Sub


Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete

End Sub


Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function

--
HTH...

Jim Thomlinson


"SteveDB1" wrote:

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.

Hi Jim,
Thanks for the response.
I copied your code, and after I set up a trial worksheet I ran it.
I obtain an error, stating that
LastCell = nothing.
I've gone through the code to see if I missed something/anything, and found
that everything is as you posted it-- except that I removed the comment '
character
from the

'Dim lngVisible As Long

For Each wks In Worksheets
'lngVisible = wks.Visible
CompactSheet wks
'wks.Visible = lngVisible

as I understood your comments:
(remove the commenting to toggle the visible setting if you want to)
I then re-commented out those items, and obtain the same error.




"Jim Thomlinson" wrote:

You error occures because you can not select a hidden worksheet. That being
said you do not have to unhide the worksheets to do your compatcting. Try
this code...



Sub CompactAllSheets()
Dim wks As Worksheet
'Dim lngVisible As Long

For Each wks In Worksheets
'lngVisible = wks.Visible
CompactSheet wks
'wks.Visible = lngVisible
Next wks

End Sub


Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete

End Sub


Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function

--
HTH...

Jim Thomlinson


"SteveDB1" wrote:

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.

Well, whatever it was that my problem consisted of, it works now.
I decided to change all 3 into another, stand alone module, and it "just
worked."
I think this guy is now a resolved issue....
Thank you very much!!!
Best.

"Jim Thomlinson" wrote:

You error occures because you can not select a hidden worksheet. That being
said you do not have to unhide the worksheets to do your compatcting. Try
this code...

(remove the commenting to toggle the visible setting if you want to)

Sub CompactAllSheets()
Dim wks As Worksheet
'Dim lngVisible As Long

For Each wks In Worksheets
'lngVisible = wks.Visible
CompactSheet wks
'wks.Visible = lngVisible
Next wks

End Sub


Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete

End Sub


Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function

--
HTH...

Jim Thomlinson


"SteveDB1" wrote:

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.