Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding columns in grouped worksheets
I have an Excel 2003 workbook with 2 worksheets that I manipulate in
the grouped mode. There is a row on one sheet named "Bid row". The user can put an x in rows that he wants to have visible after a macro runs. The macro selects that named range, unhides all the columns, then uses SpecialCells(xlCellTypeBlanks) to select the blanks, then hides the blank columns. It works fine when I follow the procedure manually and have two worksheets grouped. The worksheet with the named range is selected, but, the columns on both worksheets are kept in synch; that is if there is a blank on sheet one, the column is hidden on both sheet one and sheet 2. However, when I automate this procedure the columns on the second sheet are not hidden. The get selected, but they don't hide. Can anyone see where I am doing something wrong in this code, or is this a limitation in progrmatic manipulation of grouped worksheets. Sub Format_View(R) Sheets(Array("LOE Separate", "Quote")).Select Sheets("Quote").Activate Range(R).Select Selection.EntireColumn.Hidden = False Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireColumn.Hidden = True End Sub R is the name of the row that is used to control whether or not a column remains visible. It appears to be selected on both grouped sheets as desired, but, the columns are only hidden on the Quote worksheet. Thanks Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding columns in grouped worksheets
Macros can only work on one sheet at a time... Try this...
Sub Format_View(R) Dim rngToHide As Range On Error Resume Next With Sheets("Quote") Set rngToHide = .Range(R).SpecialCells(xlCellTypeBlanks) .Cells.EntireColumn.Hidden = False End With Sheets("LOE Seperate").Cells.EntireColumn.Hidden = False On Error GoTo 0 If rngToHide Is Nothing Then MsgBox "Sorry. Nothing to hide." Else rngToHide.EntireColumn.Hidden = True Sheets("LOE Seperate").Range(rngToHide.Address _ ).EntireColumn.Hidden = True End If End Sub -- HTH... Jim Thomlinson "Ken" wrote: I have an Excel 2003 workbook with 2 worksheets that I manipulate in the grouped mode. There is a row on one sheet named "Bid row". The user can put an x in rows that he wants to have visible after a macro runs. The macro selects that named range, unhides all the columns, then uses SpecialCells(xlCellTypeBlanks) to select the blanks, then hides the blank columns. It works fine when I follow the procedure manually and have two worksheets grouped. The worksheet with the named range is selected, but, the columns on both worksheets are kept in synch; that is if there is a blank on sheet one, the column is hidden on both sheet one and sheet 2. However, when I automate this procedure the columns on the second sheet are not hidden. The get selected, but they don't hide. Can anyone see where I am doing something wrong in this code, or is this a limitation in progrmatic manipulation of grouped worksheets. Sub Format_View(R) Sheets(Array("LOE Separate", "Quote")).Select Sheets("Quote").Activate Range(R).Select Selection.EntireColumn.Hidden = False Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireColumn.Hidden = True End Sub R is the name of the row that is used to control whether or not a column remains visible. It appears to be selected on both grouped sheets as desired, but, the columns are only hidden on the Quote worksheet. Thanks Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding columns in grouped worksheets
Jim
It is working great. I had to change it a little but your use of rngToHide.Address saved the day. In fact, it saved so much of the day, I think I will go home and play golf. Thanks a lot. Ken final code (I may put some of the error checking back in eventually) Sub Format_View(R) Dim rngToHide As Range With Sheets("Quote") Set rngToHide = .Range(R).SpecialCells(xlCellTypeBlanks) .Cells.EntireColumn.Hidden = False rngToHide.EntireColumn.Hidden = True End With With Sheets("LOE Separate") .Cells.EntireColumn.Hidden = False .Range(rngToHide.Address).EntireColumn.Hidden = True End With End Sub On Apr 23, 1:13*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Macros can only work on one sheet at a time... Try this... Sub Format_View(R) * * Dim rngToHide As Range * * On Error Resume Next * * With Sheets("Quote") * * * * Set rngToHide = .Range(R).SpecialCells(xlCellTypeBlanks) * * * * .Cells.EntireColumn.Hidden = False * * End With * * Sheets("LOE Seperate").Cells.EntireColumn.Hidden = False * * On Error GoTo 0 * * If rngToHide Is Nothing Then * * * * MsgBox "Sorry. Nothing to hide." * * Else * * * * rngToHide.EntireColumn.Hidden = True * * * * Sheets("LOE Seperate").Range(rngToHide.Address _ * * * * *).EntireColumn.Hidden = True * * End If End Sub -- HTH... Jim Thomlinson "Ken" wrote: I have an Excel 2003 workbook with 2 worksheets that I manipulate in the grouped mode. There is a row on one sheet named "Bid row". *The user can put an x in rows that he wants to have visible after a macro runs. The macro selects that named range, unhides all the columns, then uses SpecialCells(xlCellTypeBlanks) to select the blanks, then hides the blank columns. *It works fine when I follow the procedure manually and have two worksheets grouped. *The worksheet with the named range is selected, but, the columns on both worksheets are kept in synch; that is if there is a blank on sheet one, the column is hidden on both sheet one and sheet 2. However, when I automate this procedure the columns on the second sheet are not hidden. *The get selected, but they don't hide. Can anyone see where I am doing something wrong in this code, or is this a limitation in progrmatic manipulation of grouped worksheets. Sub Format_View(R) * * Sheets(Array("LOE Separate", "Quote")).Select * * Sheets("Quote").Activate * * Range(R).Select * * Selection.EntireColumn.Hidden = False * * Selection.SpecialCells(xlCellTypeBlanks).Select * * Selection.EntireColumn.Hidden = True End Sub R is the name of the row that is used to control whether or not a column remains visible. *It appears to be selected on both grouped sheets as desired, but, the columns are only hidden on the Quote worksheet. Thanks Ken- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Same footer for worksheets (grouped) | Excel Programming | |||
hiding formulas with "Grouped' columns | Excel Discussion (Misc queries) | |||
hiding formulas with "Grouped' columns | Excel Discussion (Misc queries) | |||
Hiding Columns in Worksheets saved in CSV Format | New Users to Excel | |||
Protected worksheet, showing and hiding Grouped detail | Excel Discussion (Misc queries) |