Hide Rows and Columns
Hi Tom,
I have entered the following:
Sub Hide_Rows_and_Columns()
Dim sh As Worksheet, ncol As Long
Dim rng As Range, col As Long
For Each sh In Worksheets
sh.Rows.Hidden = False
sh.Columns.Hidden = False
Set rng = sh.Cells.SpecialCells(xlLastCell)
sh.Rows(rng.Rows(rng.Rows.Count).Row _
+ 1 & ":65536").Hidden = True
col = rng.Columns(rng.Columns _
.Count).Column
ncol = 256 - col
sh.Columns(col + 1).Resize(, ncol).Hidden = True
Next
End Sub
But when I try to run the Macro, it comes up with:
"Run Time Error 1004"
"Application Defined or Object defined error"
and when I click "Debug" it highlights in yellow, the
line, "sh.Columns(col + 1).Resize(, ncol).Hidden = True"
What does this mean?? And why is it doing this?
Thanks again for your help,
Stuart
-----Original Message-----
Sub Tester1()
Dim sh As Worksheet, ncol As Long
Dim rng As Range, col As Long
For Each sh In Worksheets
sh.Rows.Hidden = False
sh.Columns.Hidden = False
Set rng = sh.Cells.SpecialCells(xlLastCell)
sh.Rows(rng.Rows(rng.Rows.Count).Row _
+ 1 & ":65536").Hidden = True
col = rng.Columns(rng.Columns _
.Count).Column
ncol = 256 - col
sh.Columns(col + 1).Resize(, ncol).Hidden = True
Next
End Sub
from A1 to xllastcell will always include the last cell
with data, but may
overstate the range containing data. Also, you might
want to include error
checking to make sure that there are at least some
rows/columns to hide.
--
Regards,
Tom Ogilvy
,"Stuart Steven"
wrote in message
...
Hi,
Is there a way of running a macro (or worksheet
function)
that, for every worksheet within a workbook, looks at
each
sheet and finds the last column that contains data
and "hides" every column thereafter, and also looks for
the last row that contains data and "hides" every row
thereafter??
Many Thanks,
Stuart.
.
|