Help! VBA Code to hide specific columns containing selected text from drop down list

[dvdlev]

Hi,

I am trying to write a VBA code that would allow me to hide all columns containing whatever text/option has been selected from a drop down list in excel.

For example, all the options are within columns D:Z. The drop down list contains "Cat", "Dog", "Horse", "Fish". If I am selected on the Cat option in the drop down list then I want to hide all columns in "D:Z" containing Dog/Horse/Fish. On the other hand, if I am selected on "Dog", I want all columns containing "Cat", Horse", "Fish" to be hidden in columns "D:Z" and so on.

I am fairly new to writing VBA code and any help would be MUCH appreciated.

Thank you !

 

[JLGWhiz]

Is the drop down list associated with a cell on the worksheet, or is it a combobox and if it is a combobox then where is it located (worksheet or Userform)?

 

[offthelip]

You haven't specified where your dropdown list is, SO I have assumed the result is in Cell A1:

Sub test2()
animal = Cells(1, 1)
If animal <> "" Then
lastrow = ActiveSheet.UsedRange.Rows.Count
inarr = Range(Cells(1, 1), Cells(lastrow, 26))
Columns("D:Z").Hidden = True
 For i = 4 To 26
  For j = 1 To lastrow
     If animal = inarr(j, i) Then
      Columns(i).Hidden = False
      Exit For
     End If
  Next j
Next i
End If

 

[dvdlev]

Is the drop down list associated with a cell on the worksheet, or is it a combobox and if it is a combobox then where is it located (worksheet or Userform)?

its a drop down list, the names are on another sheet in the background

 

[dvdlev]

You haven't specified where your dropdown list is, SO I have assumed the result is in Cell A1:

Sub test2()
animal = Cells(1, 1)
If animal <> "" Then
lastrow = ActiveSheet.UsedRange.Rows.Count
inarr = Range(Cells(1, 1), Cells(lastrow, 26))
Columns("D:Z").Hidden = True
For i = 4 To 26
  For j = 1 To lastrow
     If animal = inarr(j, i) Then
      Columns(i).Hidden = False
      Exit For
     End If
  Next j
Next i
End If

I ran it but that didn't work do anything. was I supposed to change anything in your code? I just pasted it in and ran. also, it was a drop down list, the names are on another sheet in the background. thanks for helping out!

 

[JLGWhiz]

The question was: "Is the drop down in cell A1?" If it is not in cell A1 then the code will not produce any results because it is referencing the wrong place to look. The code below also uses A1 as the reference cell, if it is not that cell where you have the drop down, then change the code to reflect the cell where the dropdown list is. Please respond to the questions asked, they are asked for a reason.

Sub t()
Dim fn As Range, adr As String, i As Long
Sheet1.Range("D:Z").EntireColumn.Hidden = True
    For i = 4 To 26
        If Application.CountIf(Sheet1.Columns(i), Sheet1.Range("A1").Value) > 0 Then
            Columns(i).Hidden = False
        End If
    Next
End Sub

Where the llist for the dropdown is derived from is irrelevant for this purpose. We need to know where on the sheet the drop down box is located.

 

[dvdlev]

Thank you so much!!! Its working!

 

[JLGWhiz]

Thank you so much!!! Its working!

You're welcome,
regards, JLG