Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using macros to hide columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using macros to hide columns
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. -- Regards, Tom Ogilvy "PCLIVE" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using macros to hide columns
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 -- bill k ------------------------------------------------------------------------ bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821 View this thread: http://www.excelforum.com/showthread...hreadid=472751 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using macros to hide columns
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" wrote in message ... 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 -- bill k ------------------------------------------------------------------------ bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821 View this thread: http://www.excelforum.com/showthread...hreadid=472751 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using macros to hide columns
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 -- bill ----------------------------------------------------------------------- bill k's Profile: http://www.excelforum.com/member.php...info&userid=82 View this thread: http://www.excelforum.com/showthread.php?threadid=47275 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to Hide columns in spreadsheet but NOT hide data in chart. | Charts and Charting in Excel | |||
Hide/Unhide columns using button on top over relevant columns | Excel Discussion (Misc queries) | |||
Excel button :: Filter columns by value - possible? Additionally, hide certain columns | Excel Programming | |||
Hide sheets before macros can run | Excel Programming | |||
How can you hide the macros ? | Excel Programming |