Solved Macro to hide/unhide based on multiple column headers

Microsoft Office 2007 professional (aca...
March 17, 2015 at 02:18:57
Specs: Excel 2007
Hello everyone

I need to make Macro to do the below

I have table with 2 column headers ( Month , Account)

It’s around 600 column with 50 accounts * 12 month starting from Column 7

I need to create multiple Macros to have multiple views

So for example I need to have 15 account Jan & Feb numbers so I need to macro to hide all the columns that doesn’t meet that criteria

I just need the base to have multiple month and multiple account as a criteria and I’ll manage my multiple Macros 

Thanks in advance

See More: Macro to hide/unhide based on multiple column headers

March 17, 2015 at 02:22:10
Currently this is the one i tried to use and it works to keep only one column, when i try to add criteria it hides all columns

Sub ColumnHider5()

Dim q As Long, w As Long

q = Cells(4, Columns.Count).End(xlToLeft).Column
For w = 7 To q
If Cells(4, w).Value <> "Jan" Or Cells(3, w).Value <> "NR" Then
Cells(4, w).EntireColumn.Hidden = True
End If
Next w

End Sub

message edited by Solika

Report •

March 17, 2015 at 11:05:45
✔ Best Answer
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. The How-To will explain the use of the "pre" tags. Thanks!

As far as your question, the following code is kind of a brute force method to achieve your goal. If I knew more about the layout of your spreadsheet or the process you are using, I'm sure I could streamline it a bit. You asked for "the base" and this is pretty basic.

The first thing you will notice is that am not individually hiding the columns I don't want to see. Instead, I am hiding all of them first and then making visible just the ones I want to see.

In addition, the code assumes your company names are in Row 3 and that there is a Company name above each month, as shown below. Feel free to correct me if I am wrong.

   A         G      H  ...  R      S     T   ... AD     AE   AF  ... AP
3 Company    NR     NR  ... NR     BB    BB  ... BB     MN   MN  ... MN
4  Month     Jan    Feb ... Dec    Jan   Feb ... Dec    Jan  Feb ... Dec

Finally, as I said earlier, this is a brute force method with the company names (e.g. NR and MN) and the months (e.g. Jan and Feb) hard coded into the macro. As written, you would need an additional "Or" clause for each additional company or month that you wanted to see. In real life, these values could probably be put into a range of cells and looped through/pulled into the code, but since I don't know enough about your spreadsheet layout I didn't offer any suggestions related to that method.

Let me know what you think...

Sub ColumnHider()
Dim lastCol As Long, nxtCol As Long
'Determine last column with data in Row 4
     lastCol = Cells(4, Columns.Count).End(xlToLeft).Column
'Hide all columns in table (G:Last Column
     Range(Cells(1, 7), Cells(1, lastCol)).EntireColumn.Hidden = True
'Unhide Jan & Feb for Companies NR & MN
      For nxtCol = 7 To lastCol
        If (Cells(3, nxtCol).Value = "NR" Or _
            Cells(3, nxtCol).Value = "MN") And _
              (Cells(4, nxtCol).Value = "Jan" Or _
               Cells(4, nxtCol).Value = "Feb") Then
                  Cells(4, nxtCol).EntireColumn.Hidden = False
        End If
      Next nxtCol
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question