0

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 drop down list options are within columns I:FT. The drop down list contains "Cat", "Dog", "Horse", "Fish", " " <—blanks. If I am selected on the blank " " option in the drop down list then I want to hide all columns in “I:FT” containing blanks.

When it does hide the columns containing blanks, all rows in the column must be blank when the I:FT range is selected. The range of these cells are 416R x 169C.

Additionally, I need it to only account for visible rows. I want it to have the ability to filter on a column (such as part number) and have the macro run and only hide the columns with blanks based off whatever is shown from the filtered part number.

The drop down list is associated cell H2 and the list is on another sheet in the background

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

Thank you !

Improve this question

1 Answer 1

Reset to default
0

To get you started, see the recently answered question about hiding rows based upon a drop down box. You can use this to adapt it for your own purposes.

Multiple Non-contiguous rows in excel Based on cell value

If the columns may change order from time to time, then hard coding the column numbers would not be sufficient. In that case, you could use a couple of methods to make sure the correct column(s) is hidden, which would work by assigning the columns to named ranges first:

  • Worksheet.Range("CELL REFERENCE FOR 1st ROW").EntireColumn.Hidden = True
  • Worksheet.Columns("COLUMN REFERENCE").Hidden = True

Once you have attempted that and are still stuck. Come back here, explain your progress and share what VBA code you have written so far.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Sub Hide_blank_columns() Dim SearchWord As String Dim SearchColumn As Range Dim SearchResult As Range SearchWord = Range("h2").Value 'drop down list i = Cells(Rows.Count, "i").End(xlUp).Row j = Range("Ft" & 400).Column For x = 9 To j ' Looping clmn = Split(Cells(1, x).Address, "$")(1) Set SearchColumn = Range(clmn & "1:" & clmn & i) Set SearchResult = SearchColumn.Find(SearchWord, LookIn:=xlValues, lookat:=xlWhole) If Not SearchResult Is Nothing Then Columns(x).Hidden = True Else End If Next End Sub
someone helped create this. its not fully working but I think its close. the problem with it is it is hiding too many columns. it hide everything (including those not blank)
You got a good start. Ran your code in basic mocked up file using months as headers. I left some columns within table completely blank. Seemed to work fine for me; hiding each completely blank column. In your original post you stated "When it does hide the columns containing blanks, all rows in the column must be blank when the I:FT range is selected." E.g. col A with header & few blank cells below = not be hidden. Col B completely blank (even the header) = would be hidden. Have I interpreted your problem correctly? Screenshot of (portion) of table would be useful.
i would definelty post but i cant seem to figure that out on here. im a new overflow user sorry lol.
but, i think youre on to something. to more clearly define what im trying to accomplish- i want to hide columns with blanks UNLESS there is a value >=1
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.