hiding columns in grouped worksheets

[Ken]

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

 

[Jim Thomlinson]

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

 

[Ken]

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