Hide Rows and Columns
 

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.

Hide Rows and Columns
 

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.

Hide Rows and Columns
 

Hey for finding the last row which has the data you can use th
following.
lastrw = Cells(Rows.Count, "B").End(xlUp).Row

It checks in the column "B" for data and gives the last row.

Based on this, you can use the following for hiding the rows
Sheets("Sheet1").Rows("lastrw:65536").Hidden = True

Hope this helps.

Samee

--
Message posted from http://www.ExcelForum.com

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.


.

Hide Rows and Columns
 

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

Hide Rows and Columns
 

As written:
Sheets("Sheet1").Rows("lastrw:65536").Hidden = True

will just give you an error unless you have a defined name "lastrw"

--
Regards,
Tom Ogilvy

"sameerce " wrote in message
...
Hey for finding the last row which has the data you can use the
following.
lastrw = Cells(Rows.Count, "B").End(xlUp).Row

It checks in the column "B" for data and gives the last row.

Based on this, you can use the following for hiding the rows
Sheets("Sheet1").Rows("lastrw:65536").Hidden = True

Hope this helps.

Sameer


---
Message posted from http://www.ExcelForum.com/