Excel VBA - hide/unhide “na” columns

Question

First off, i want you to know that im very new to VBA. I can manage to copy and paste certain data and adapt ranges according to my needs but that is all. Now to my problem:

I have a row consisting of 31 columns (E27:AI27). All the cells within this range get their input (a number from 1-31) from other cells outside of this range, Let's say from a couple of drop down lists. Depending on how I twerk these drop-down lists the sequence of the numbers in that specific range (E27:AI27) can look like the following alternatives:

Alt. 01: 1 2 3 4 5................................29 30 31 (31 cells total)

Alt. 02: 2 3 4 5 6...........................29 30 31 na (31 cells total)

Alt. 03: 3 4 5 6 7...........................30 31 na na (31 cells total)

Alt. 04: 4 5 6 7 8......................30 31 na na na (31 cells total)

etc etc etc.

Alt. 30: 30 31 na......................na na na na na (31 cells in total)

Alt. 31: 31 na na..................... na na na na na (31 cells in total)

Now what I want to do is to automatically, temporarily, hide those columns that contain string "na" within them. When I then re-twerk my drop down lists again, i want to be able to unhide those columns that change back from "na" to a number. For example, i want to be able to shift between alternative 1 and alternative 2 (see above), depeding on how i twerk my drop down lists. Shifting between these two alternatives would mean that AI27 would go from showing, to hiding, to showing, to hiding and so on (while E27AH27 would all be showing since they would all have numbers inside then, 1-30.

Last but not least, there are two drop down lists that control the values of the cells E27:AI27. Drop down list called month (C18) and drop down list called day (D18). The former (C18) sets the days for a month. If C18=February then there will be 28 days and the last two columns (AH:AI) would be "na". In addition to that the latter drop down list (D18) sets the start day, namly the first number (in cell E27) in the sequence. If D18=21, then the alternative for february month would be:

21 22 23 24 25 26 27 28 na na na na na na na na na na na na.... (containing 23 na)

Can anyone please help me set up an easy and short VBA code for this, if possble? Highly appreciated

kolcinx kolcinx · Accepted Answer · 2015-03-14T10:55:26

Try this one :

Private Sub Worksheet_Change(ByVal Target As Range)
    'change the "E23" with the cell with your dropdown
    If Not Intersect(Target, Range("E23")) Is Nothing Then
        Call UnhideHideColumns
    End If
End Sub

Sub UnhideHideColumns()

Dim bytColumnCheck As Byte
Dim blnNeedToUnhide As Boolean
Dim intFirstNAColumn As Integer

'check all columns and find out if they are already hidden
For bytColumnCheck = 5 To 35
    'if any of the columns is hidden, unhide all the columns to the right and exit loop
    If Cells(1, bytColumnCheck).EntireColumn.Hidden = True Then
        Range(Cells(1, bytColumnCheck), Cells(1, 35)).EntireColumn.Hidden = False
        Exit For
    End If
Next bytColumnCheck

'find first occurence of "na" in values, if any exists
If Not Cells.Find("na", LookIn:=xlValues, after:=Range("E27")) Is Nothing Then
    intFirstNAColumn = Cells.Find("na", LookIn:=xlValues).Column

    'now hide the columns to the right of the first "NA", including
    Range(Cells(1, intFirstNAColumn), Cells(1, 35)).EntireColumn.Hidden = True
End If

End Sub

Note that the Call UnhideHideColumns will be called every time the cell with the dropdown changes, even let's say from Set1 to Set1, or from 5 to 5. So it can trigger the macro unnecessary often.

Excel VBA - hide/unhide “na” columns

2 Answers