Using macros to hide columns

[PCLIVE]

I saw another post regarding problems with hiding columns using a macro.

I run the macro recorder, group several sheets together, and then select
multiple columns on the sheet. Goto format and select the option to
columns, hide. I stop the macro recorder. When checking each of the sheets
that were grouped together, the selected columns on each worksheet has been
hidden. That is what I want. If I unhide everything and then run the macro
I just recorded, it only hides the columns on the active sheet. Does anyone
know why this is happening and how I can get it to the same columns on all
of the grouped sheets, just as if I was doing it manually.

This is what gets recorded my the macro recorder.

Rows("1:17").Select
Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Select
Sheets("Sheet4").Activate
Selection.EntireRow.Hidden = True
Rows("25:39").Select
Selection.EntireRow.Hidden = True
Columns("B:F").Select
Range("B18").Activate
Selection.EntireColumn.Hidden = True



Thanks,
Paul

 

[Tom Ogilvy]

for the most part, VBA does not support actions performed on grouped sheets.
In some cases, you can work around it by using Selection, but it appears
that is not the case for you. Just loop through the sheets and perform the
action.

 

[bill k]

I can't explain why it didn't work. Probably something about active
windows but the following is a good alternative.

Sub hiderowcol()
Dim i As Integer
For i = 2 To 4
Sheets(i).Select
Rows("1:17").Select
Selection.EntireRow.Hidden = True
Rows("25:39").Select
Selection.EntireRow.Hidden = True
Columns("B:F").Select
Selection.EntireColumn.Hidden = True
Next

End Sub

 

[PCLIVE]

Thanks Bill,

Unfortunately, the sheets are not in succession and therefore this would not
be effective. If it were possible to have a macro activate the next
worksheet within a set of grouped worksheets, that could possibly work. But
I'm not sure that can be done.

Thanks again,
Paul

 

[bill k]

You could give the sheets that need the hidden columns a "label"
i.e sheet2hhh, sheet3hhh , sheet5hhh, sheet7hhh
and use that distinction in the macro

Sub onlysome()
Dim mysht As Worksheet
For Each mysht In ThisWorkbook.Worksheets
If InStr(mysht.Name, "hhh") Then
mysht.Select
Rows("1:17").Select
Selection.EntireRow.Hidden = True
Rows("25:39").Select
Selection.EntireRow.Hidden = True
Columns("B:F").Select
Selection.EntireColumn.Hidden = True

End If
Next mysht
End Su