Excel: Macro won't recognize group / ungroup

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

sharp01

Thread Starter
Joined
Apr 26, 2017
Messages
2
I'm trying to build a macro for an excel spreadsheet that I've built. I need different headers for 3 of the 5 pages. To accomplish this, I've grouped rows in order to be able to hide/unhide rows 2-6, 12-16 and 18-19 as need be. It works out great when I do this manually, but when I create the macro the macro fails to recognize when I use my mouse to press the + or - button which hides or unhides a group. I'm not sure how to solve this? Below is the code that appears when I look at the macro via VBA editor. I'm new to this and this is my first post so go easy on me! Thanks!!

Sub Print_Worksheet_Shortcut()
'
' Print_Worksheet_Shortcut Macro
' This macro is a shortcut which enables the worksheet to be printed with the proper headers that the City of Sacramento requires.
'
' Keyboard Shortcut: Ctrl+Shift+H
'
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=4, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut From:=5, To:=5, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
End Sub
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
Have you recorded a macro foor Group and one for Ungroup?
I haven't done this myself but it's an idea to look and see how it works.
I can help you with the vba code once you have it.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
I see what you mean, I recorded and nothing is recorded.
Probably have to revert to a full macro to select what you want to hide before printing.
I would need to know what you require when you print and what needs to happen after you have printed.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
Some vba code:

The first are hardcoded to hide the said ranges

Code:
Sub hideRows26()
Range("A2:A6").EntireRow.Hidden = True
End Sub

Sub unhideRows26()
Range("A2:A6").EntireRow.Hidden = False
End Sub

Sub hideRows1216()
Range("A12:A16").EntireRow.Hidden = True
End Sub

Sub unhideRows1216()
Range("A12:A16").EntireRow.Hidden = False
End Sub

Sub hideRows1819()
Range("A12:A16").EntireRow.Hidden = True
End Sub

Sub unhideRows1819()
Range("A12:A16").EntireRow.Hidden = False
End Sub
This one below you can use for any range, just pass the string for the range

ie: HideRow sRange:="$A2:$A6" will do the same as the hiderows26 macro

Code:
Sub HideRows(sRange As String)
Range(sRange).EntireRow.Hidden = True
End Sub

Sub unHideRows(sRange As String)
Range(sRange).EntireRow.Hidden = False
End Sub
 
Last edited:

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
You could then have a resulting code something like this:

Code:
Sub Print_Worksheet_Shortcut()
'
' Print_Worksheet_Shortcut Macro
' This macro is a shortcut which enables the worksheet to be printed with the proper headers that the City of Sacramento requires.
'
' Keyboard Shortcut: Ctrl+Shift+H
'

    unHideRows sRange:="$A$1:$A$80"
    HideRows sRange:="$A$2:$A$6"
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
        :=True, IgnorePrintAreas:=False
    unHideRows sRange:="$A$2:$A$6"
    HideRows sRange:="$A$12:$A$16"
    ActiveWindow.SelectedSheets.PrintOut From:=2, To:=4, Copies:=1, Collate _
        :=True, IgnorePrintAreas:=False
    unHideRows sRange:="$A$12:$A$16"
    HideRows sRange:="$A$18:$A$19"
    ActiveWindow.SelectedSheets.PrintOut From:=5, To:=5, Copies:=1, Collate _
        :=True, IgnorePrintAreas:=False
    unHideRows sRange:="$A$1:$A$80"
End Sub
 

sharp01

Thread Starter
Joined
Apr 26, 2017
Messages
2
Hi Hans-
Thank you for the response. I think I understand what you're telling me. I ran the macro hiding the unhiding the rows manually and it worked perfect. I'll study the code that you sent a little closer so that I better understand it. This is the very first time I've seen VBA code so it's like looking at the Chinese alphabet to me. I appreciate you taking the time to respond!

Cheers,

Rob
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
Hi Rob,
If you have questions just holler.
VBA is really nothing more than step-by-step instructions of what you want to do.
You just translate the actions you want to do be carried out to code, vba code is quite straight forward and practice and time, especially time, is what you need most.
You'll see that all at once the Chinese alphabet will become clearer :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top