1

I am trying to create a macro in Excel 2016 that auto hides columns based on a string contained in a different cell. Right now, the Range R1:GJU1 is a formula that populates an X with an IF formula.

This is my code:

Sub Hide_Columns_Containing_Value()

    Dim c As Range

    For Each c In Range("R1:GJU1").Cells
        If c.Value = "X" Then
            c.EntireColumn.Hidden = False
        End If
    Next c

End Sub

I get this error:

Run-time error '13':

Type mismatch

Any help is appreciated!

vash1422
  • 45
  • 1
  • 11

1 Answers1

0

I am typing this from my phone so I can not test it, but doing this:

Columns(c.column).EntireColumn.Hidden = True

will work. Basically, what you are trying to do is just say Cell.Hide which will not work. The EntireColumn.Hidden will work when referencing a column. So, you should say, hide the column of this cells' column.

I also noticed you are setting it to False which means, Unhide. I am guessing that was done erroneously, unless you are in fact trying to Unhide it.

The full code should be:

Sub Hide_Columns_Containing_Value()

Dim c As Range

For Each c In Range("R1:GJU1").Cells
    If c.Value = "X" Then
        Columns(c.column).EntireColumn.Hidden = True
    End If
Next c

End Sub

Let me know if it works!

Busse
  • 853
  • 6
  • 15
  • Thanks for weighing in. So my worksheet has Columns hidden for the user initially. I have a drop down (using a Data Validation range) that changes R1:GJU1 to an 'X' with an IF statement (mostly because I could not get the macro to work with my drop down). When I use your code, it does work! Now, what if I wanted the user to be able to select different options on the drop down to 're-hide' the prior columns (on the old criteria per drop down) and only show the new columns? – vash1422 Jul 14 '17 at 19:41
  • Ah that makes much more sense! Did the code help at all or no? – Busse Jul 14 '17 at 19:43
  • Sorry, edited my prior comment. The code did help, now trying to optimize to make it more dynamic to only display a value in the particular cell. – vash1422 Jul 14 '17 at 19:46
  • So you can tap into the drop down list itself and call a macro for when it's changed. If changed to a certain value then `do stuff`. But, that question is drastically different than this. For the sake of the usefulness of the site, you should ask that as a new question as they are quite different from one another. If my answer was correct do me a favor and Mark it as the right answer. :) Post the new question and once it's posted I'll help on that one too! – Busse Jul 14 '17 at 19:53