I bet it was the second (or more) time through. I've found that sometimes
hiding columns can change the lastcell.

But this version of Tom's code didn't blow up for me.

Option Explicit
Sub Hide_Rows_and_Columns()
Dim sh As Worksheet
Dim rng As Range
Dim icol As Long
Dim iRow As Long

For Each sh In Worksheets
sh.Rows.Hidden = False
sh.Columns.Hidden = False
Set rng = sh.Cells.SpecialCells(xlLastCell)

iRow = rng.Rows(rng.Rows.Count).Row
icol = rng.Columns(rng.Columns.Count).Column

If iRow < 65536 Then
sh.Rows(iRow + 1 & ":65536").Hidden = True
End If

If icol < 256 Then
sh.Columns(icol + 1 & ":256").Hidden = True
End If
Next sh
End Sub

Stuart Steven wrote:

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.


.


--

Dave Peterson