Macro to Hide

[Tennisguuy]

I have a spreadsheet where I track by month some data. The data is displayed in cells A5:AA178. There are times that I want to only see certain months. The spreadsheet tracks the data by month and cumulative.

I am not good with writing macros and I was going to use the macro recorder in excel 10 to hide the sections that I don't want to see. For example, if I only want to see the data to March I would want to hide the rows from April to Dec. Then have it set the print range if I want to print it. I was going to create macro buttons from the macros I recorded to click on the button for the months that I only wanted to see.

Then I wondered if there was an easier way to do it.

I was going to put the macro button at the top of the sheet from A1:B3. As I mentioned above the data starts in A5:AA178

The months are displayed in the following sections

Jan A7:A19
Feb A7:AA34 the data is cumulative so when I display Feb I want to see Jan to Feb and so forth which is why it start with A7
March A7:AA49
April A7:AA64
May A7:AA78 I have a page break inserted on line 78
June A7:AA93
July A7:AA107
Aug A7:AA121
Sept A7:AA135
Oct A7:AA148 I have a page break inserted on line 148
Nov A7:AA163
Dec A7:AA178

 

[JimM]

Rather than go down the macro route have you had a look to see if Filters will do what you want?

 

[Tennisguuy]

I didn't try filters because I didn't think they would work because there is no particular line item for the filter to work.

 

[EmmaOD91]

Not sure if this is necessarily the right way to do it but this would be my approach:

Sub hideStuff()

    Dim month As String
    Dim cutOff As Integer
    Dim maxRow As Integer
    Dim currentlyHidden As Boolean 'Defaults to false
    
    maxRow = 178
    month = Application.Caller
    
    If (month = "Jan") Then cutOff = 19
    If (month = "Feb") Then cutOff = 34
    'Continue along this route and fill in all months
    
    If (Cells(cutOff, 1).EntireRow.Hidden = True) Then currentlyHidden = True 'Check to see if row is currently hidden
    
    Range("A7:AA" & maxRow).EntireRow.Hidden = False 'Unhide everything
    If (currentlyHidden = False) Then Range("A7:AA" & cutOff).EntireRow.Hidden = True 'If it wasn't hidden before, hide it

End Sub

To get the application.caller bit to work. Make 12 buttons at the top of the screen (I would just use rectangle shapes). You'll need to rename each button with the format "Jan", "Feb", "Mar" etc. Label them, then assign the hideStuff macro to each. Should then work.

The way I wrote it above toggles hiding so that if you press January once, it will hide it. Press it again and it will unhide everything.

That clear enough or do you need more instruction on how to rename buttons?

 

[Tennisguuy]

Thanks, I did what you described above and I got the following error message Run-time error '1004": Application-defined or object-defined error. When I rename macros do they all have to be only three strings (i.e. Jan, Feb) or if I name Apr April on the macro name will that cause a problem. I named all of the months with just three strings and still got the above error. I copied the code from above and just pasted it in vba. I hit Alt +F11 key to open up VBE and right clicked on the sheet and selected insert and then module and pasted the above code.

 

[EmmaOD91]

So the macro should always have the same name, it's just the button that you use to call the macro which should have a different name.

Each button calls the hideStuff macro, but the value of month is set to application.caller (application.caller is the name of the button used to activate the macro)

Can you make sense of the code with that information? All month names should be three characters in the code and in the button names. It is important that you're consistent

 

[Tennisguuy]

Ok I see my problem at least I think I do I didn't read the code. You wanted me to fill in the rest of the code for the months. Let me do that and then try.

 

[Tennisguuy]

I updated the code but still got the same error. This is the code I used

Sub hideStuff()

Dim month As String
Dim cutOff As Integer
Dim maxRow As Integer
Dim currentlyHidden As Boolean 'Defaults to false

maxRow = 178
month = Application.Caller

If (month = "January") Then cutOff = 19
If (month = "February") Then cutOff = 34
If (month = "March") Then cutOff = 49
If (month = "April") Then cutOff = 64
If (month = "May") Then cutOff = 78
If (month = "June") Then cutOff = 93
If (month = "July") Then cutOff = 107
If (month = "August") Then cutOff = 121
If (month = "September") Then cutOff = 135
If (month = "October") Then cutOff = 149
If (month = "November") Then cutOff = 163
If (month = "December") Then cutOff = 178
'Macro name on button must match the application.Caller

If (Cells(cutOff, 1).EntireRow.Hidden = True) Then currentlyHidden = True 'Check to see if row is currently hidden

Range("A7:AA" & maxRow).EntireRow.Hidden = False 'Unhide everything
If (currentlyHidden = False) Then Range("A7:AA" & cutOff).EntireRow.Hidden = True 'If it wasn't hidden before, hide it

End Sub

 

[Tennisguuy]

It cut off the rest of my message I still got the same error with the code I corrected.

 

[Tennisguuy]

I tried it both with three characters in the code and three characters on each macro button and still got the same error. To name the macro button I just right clicked on the macro button selected edit text and type in the month. When I clicked on debug when the error came up. It highlighted this in yellow If (Cells(cutOff, 1).EntireRow.Hidden = True) Then