Macro to Hide

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
686
Rather than go down the macro route have you had a look to see if Filters will do what you want?
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 4, 2013
Messages
39
Not sure if this is necessarily the right way to do it but this would be my approach:

Code:
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

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 4, 2013
Messages
39
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

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows
It cut off the rest of my message I still got the same error with the code I corrected.
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top