Hide multiple columns VBA Excel

Question

In sheet1 I need to show all columns from (C:N), where is selected month in (B2), other columns must hide. If i chose month 4 in B2, then i need to see only these columns where is date with month 4 in (C:N) and etc.

If i have full calendar of dates, how to find exact month and hide others??

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wb As Workbook
Dim ws As Worksheet
Dim entireRange As Range
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set entireRange = ws.Columns("C:N")

entireRange.EntireColumn.Hidden = False

Select Case ws.Range("B2")

  Case "1"
      ws.Range("G:N").EntireColumn.Hidden = True

  Case "5"
      ws.Range("C:F,H:N").EntireColumn.Hidden = True

  Case "6"
      ws.Range("C:G,I:N").EntireColumn.Hidden = True



End Select
End Sub

It looks like you want us to write some code for you. While many users are willing to produce code for a coder in distress, they usually only help when the poster has already tried to solve the problem on their own. A good way to demonstrate this effort is to include the code you've written so far, example input (if there is any), the expected output, and the output you actually get (console output, tracebacks, etc.). The more detail you provide, the more answers you are likely to receive. Check the FAQ and How to Ask — D.Kastier
– D.Kastier, Commented Nov 9, 2018 at 12:21
Can you add what is wrong/ what is missing with your code? Where do you struggle? — Pierre44
– Pierre44, Commented Nov 9, 2018 at 14:05

Pierre44 · Accepted Answer · 2018-11-12 11:34:09Z

1

A simple code to hide the Columns that don t fit your month would look like this:

Sub HidingColumn()

Dim i As Long

For i = 12 To 3 Step -1 '12 being your N Column

    If Month(Cells(4, i)) <> Range("B2").Value Then
        Cells(4, i).Columns.Hidden = True
    End If
Next i

End Sub

answered Nov 12, 2018 at 11:34

Pierre44

1,7312 gold badges13 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

Hide multiple columns VBA Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related