Hide Month Calender Date Column | Excel | VBA

jackbhai

New Member
Joined
May 19, 2019
Messages
18
Hide Month Calender Date Column | Excel | VBA
I have a Leave Tracker Excel where i want to Hide the Remaining Date Columns for Particular Months Which as Only 28 days or 30 days
For example
Example 1 : Assume i have Month => FEB YEAR 2019 => It as only 28 Days so remaining Days [29,30,31] Column should be Hide
Example 2 : Assume i have Month => JUNE YEAR 2019 => It as Only 30 Days so remaining Day [31] Column should be Hide
Below is the Video To see How it Appears for my excel
This my Video on Youtube => About issue => https://youtu.be/AjqX5t-3XHE
My Working Excel Sheet [Google Drive Link]
https://drive.google.com/file/d/1UBzaUFIlGUdqcfmJ-9zuR9eKf3XZXBzO/view?usp=sharing
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.

Select AG15:AI27 & with conditional format use
=AG$15=""
set colour to "no colour" & Borders to "None"
 

jackbhai

New Member
Joined
May 19, 2019
Messages
18
Fluff
if you see my above video you will come to know i am using spinner
As soon as i change the month the Month as Only 28 days so remaining Dates 29,30,31 should get hide
And if i Change to another Month using spinner then the Month as 31 days then it should display with 31 days
The main aim is i am Using Holiday table to highlight holidays that why even if the month as only 28 days then also highlighting remaining days which should be not get highlighted [29,30,31]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
Did you try my suggestion?
It will remove any highlights & borders on those cells
 

jackbhai

New Member
Joined
May 19, 2019
Messages
18
Fluff i want to Hide not to Change Color to none Bro
its a leave tracker excel bro if Month as 28 days remaining days should get Hide not Color change to None and border to none
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
In that case How about
Code:
Sub jackbhai()
   Dim Dte As Date
   Dim Clmn As Long
   Dte = DateSerial(Range("B11"), Range("B5"), 1)
   Clmn = Day(Application.EoMonth(Dte, 0)) + 5
   Range("AG:AI").EntireColumn.Hidden = False
   If Clmn < 36 Then
      Range(Cells(1, Clmn), Cells(1, "AI")).EntireColumn.Hidden = True
   End If
End Sub
Assign it to both spinners
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
You will need to do it yourself, as I have no way of supplying you the amended file.
Add the code to a regular module, then right click one of the spinners & select assign macro, then select the macro from the list that appears.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,784
Messages
5,470,747
Members
406,720
Latest member
tylergaps

This Week's Hot Topics

Top