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

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

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 -