Attendance Sheet - extra column appearing

[Nikhil Patki]

How to avoid an extra column from appearing
for e.g. my have created attendance sheet using 31 days month
but when i change month and use either Feb or 30 days month
extra column appears and have to be hidden manually.
is there any method ( formula ) which will only generate columns as per selected month without having to manually hide / unhide

 

[Lutfor Rahman Shimanto]

How to avoid an extra column from appearing
for e.g. my have created attendance sheet using 31 days month
but when i change month and use either Feb or 30 days month
extra column appears and have to be hidden manually.
is there any method ( formula ) which will only generate columns as per selected month without having to manually hide / unhide

Dear Nikhil Patki

Thanks for sharing your problem with such clarity. I am presenting an Event Procedure that will fulfil your requirements. You must keep that Event Procedure within the sheet module.

Excel VBA Event Procedure:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$D$3" Then

        Dim selectedMonth As String
        selectedMonth = Target.Value
        
        If selectedMonth = "February" Then
            Columns("AF:AH").EntireColumn.Hidden = True
        Else

            If selectedMonth = "April" Or selectedMonth = "June" Or selectedMonth = "September" Or selectedMonth = "November" Then
                Columns("AH").EntireColumn.Hidden = True
                Columns("AF:AG").EntireColumn.Hidden = False
            Else
                Columns("AF:AH").EntireColumn.Hidden = False
            End If
        End If
    End If

End Sub

OUTPUT OVERVIEW:

​

I hope the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[Nikhil Patki]

the above vba is not working.
i've changed the cell range as per actual in my sheet.
later i identified that the format you have used in example is different than what I've used.
Hence i'll share a attachment so as the vba code can be modified accordingly.
or else if i need to change my basic format a bit let me know.
but i am creating this as a template sheet hence can be used for longer period.
for the same reason I've used a little different format.