How do I create a macro in Excel to hide every other column

Question

I currently have an Excel spreadsheet with each column representing a particular type of data (i.e artist, album, title) in a music file.

What I would like to do is show changes by having two columns for each type of data (i.e artistOld, artistCurrent, albumOld, albumCurrent, titleOld, titleCurrent) but most of the time user would not want to see the old columns so what I want is a Macro that simply shows/hides the Old columns.

BTW I am ware that you can add comments (like tooltips) when you hover over a cell so in theory this would be a way to store old data, but this is not what I want. I want the user to be able to see all the data at the same time not just the old data for one particular field.

Screenshot without the old value column

Would be great if you could share your code you and things that you have already tried. — TomJohnRiddle
Instead of using a macro for this, just use a pivot table. You could move only the columns they need to see into the row fields of the pivot, and refresh it if any changes are made. It will be far easier to maintain than the VBA. — Brandon Barney
@TomJohnRiddle, I dont know how to create Macros, the spreadsheet has actually been created programmatically use apache-poi, but I assume this is the kind of thing that can be done with Macros — Paul Taylor
Asking a question like "Show me how to do this", without demonstrating any effort, is frowned upon here. Please take the tour and read up on How to Ask and why it's important to include a minimal reproducible example. — David Zemens
@DavidZemens yes, yes and if it was technology I was familair with I would do just that, but since it is technology I dont know its very difficult to even know where to begin, I didnt even know you could just record macros! — Paul Taylor

Gary's Student Gary's Student · Accepted Answer · 2018-03-02T14:31:55

This will hide all even columns:

Sub hiiddee()
    Dim i As Long
    Application.ScreenUpdating = False
        For i = 16384 To 2 Step -2
            Cells(1, i).EntireColumn.Hidden = True
        Next i
    Application.ScreenUpdating = True
End Sub

EDIT#1:

This is an adaptation of BrandonBarney's idea. It assumes that the column headers of the columns to be hidden/unhidden contain the string Old:

Sub BrandonsIdea()
    Dim i As Long, KolKount As Long, r As Range

    With ActiveSheet.UsedRange
       KolKount = .Columns.Count + .Column - 1
    End With

    For i = 1 To KolKount
        With Cells(1, i)
        If InStr(1, .Value, "Old") > 0 Then
            .EntireColumn.Hidden = Not .EntireColumn.Hidden
        End If
        End With
    Next i
End Sub

Each time it is run, it will toggle hidden/unhidden.

EDIT#2 General Instructions:

Macros are very easy to install and use:

ALT-F11 brings up the VBE window
ALT-I ALT-M opens a fresh module
paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE window as above
clear the code out
close the VBE window

To use the macro from Excel:

ALT-F8
Select the macro
Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

EDIT#3:

This version will loop over all worksheets:

Sub BrandonsIdea_2_The_Sequel()
    Dim i As Long, KolKount As Long, r As Range
    Dim sh As Worksheet

    For Each sh In Sheets
        With sh.UsedRange
           KolKount = .Columns.Count + .Column - 1
        End With

        For i = 1 To KolKount
            With sh.Cells(1, i)
                If InStr(1, .Value, "Old") > 0 Then
                    .EntireColumn.Hidden = Not .EntireColumn.Hidden
                End If
            End With
        Next i
    Next sh
End Sub

How do I create a macro in Excel to hide every other column

2 Answers