Macro to Hide

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
564
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
 
Okay, follow these steps.

1) Press Alt + F11 to open up the VBE
2) Click on Insert (at the top of the screen) then "Module"
3) Paste the code (the one you posted) into the new module:

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 = "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

Then let's just get January working for now.

4) Draw a rectangle shape on the Excel workbook
5) Click on the shape and rename it using the name box at the top left of Excel. The default name will be something like "Rectangle 1". You want to change the name to "January" Once you type the new name in the box, hit return to actually change the name of the shape. This site gives more information on the name box: What is name box?
6) Right click on the shape and choose to assign macro. Set it to the hideStuff macro.
7) (Optional) Right click on the shape and select edit text, then type in "January".
8) Test it here

If that works, just repeat 4-8 for all remaining months.
 
Last edited:
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
That worked but sorry I think I might have confused you by my original post. The macro work but it hid cells A7:AA19. What I was looking for is when I clicked on January I only wanted to see cell A7:A19 and hide cell A20:AA178 because I only want to see the January data. I will list the months below with the cells I want to hide.

January A20:AA178
February A35:AA178
March A50:AA178
April A65:AA178
May AA79:AA178
June AA94:aa178
July AA108:AA178
August AA122:AA178
September AA136:AA178
October AA150:AA178
November AA164:1178

For December which will be all the months I don't really want to hide anything as oppose to showing all of the months from A7:AA178

Sorry again for the confusion
 
Upvote 0
Try this. No toggle on this version, but I think it meets your original requirements. I haven't tested it though.

You may need to adjust the cutOff values in your list.

Code:
Sub hideStuff()

    Dim month As String
    Dim cutOff As Integer
    Dim maxRow As Integer
    
    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
    
    Range("A7:AA" & maxRow).EntireRow.Hidden = true 'hide everything
    Range("A7:AA" & cutOff).EntireRow.Hidden = false 'Unhide month and all preceeding months

End Sub
 
Last edited:
Upvote 0
I am not quite sure what you mean by adjust the cutoff value. I added the above code. However I got the error again from the initial time I tried to run macro. If you meant changing the cutoff value I did change it for January but not sure if that is what caused the error. I'm not sure if I am exactly following what the cutoff value represent but if I follow your original code when the macro work the cutoff value for January was 19 and it hide cells 6-19. Therefore for January since I want to show only up do 19 I put a cutoff value of 170 but again not sure if I am exactly following it.
 
Upvote 0
Try it without adjusting the cutoff value first - it will probably work.

If not, then what error message are you getting and on which line?
 
Upvote 0
Yes I tried it that way as well and got the Run-time Error '1004: Method 'Range' of object'_Global' failed

When I clicked on debug it highlighted this area

Range("A7:AA" & cutOff).EntireRow.Hidden = False 'Unhide month and all preceeding months
 
Upvote 0
Just double check that the name of the button is definitely "January". I suspect that it's not and so cutOff is being set to 0 which would cause that error message. Note that it's case sensitive.
 
Last edited:
Upvote 0
It's working now. Thanks for all of your help and patience. I really appreciate it. Have a good day
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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
Back
Top