Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide unused rows and columns | Excel Discussion (Misc queries) | |||
Hide columns but not the top rows | Excel Discussion (Misc queries) | |||
Hide columns and rows | Excel Worksheet Functions | |||
Hide / Unhide columns and rows | Excel Discussion (Misc queries) | |||
Hide rows or columns using + - buttons | Excel Discussion (Misc queries) |