VBA code for deleting rows based on number of days in a month

[nitiona]

Hi
I'm looking for VBA code for including in my duty rota. I have already inserted formulas (excel formula) wherein when month is selected it returns the number of days in a month including the day (Monday etc). However as you know February has 28 days and some months have 30 days etc. My formula restarts with date 1 which I want to get rid off (refer attached screenshot). Request your support in getting VBA code.
The program should only show rows based on the days in a month and hide balance rows (last 2-3 rows based on month)

Regards,
Nith

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA code for deleting rows based on number of days in a month - OzGrid Free Excel/VBA Help Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[nitiona]

Using the workbook you have posted over at Oz, here's what I would do...

Right click on the sheet tab and select View Code. This opens the sheet module where Event code automatically runs from.
Copy and paste this code into the pane that opened on the right.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim HowMany As Long
    Dim rng As Range
   
' check that only one cell changing at a time
If Target.CountLarge > 1 Then Exit Sub
' only concerned if C5 or C6
If Intersect(Target, Range("C5:C6")) Is Nothing Then Exit Sub

' initially display 31 columns
Range("D11:AH11").EntireColumn.Hidden = False

' days in this particular month
HowMany = CInt(Range("C9").Text)
If HowMany = 31 Then Exit Sub
   
'end day will be in column HowMany plus 3
Set rng = Cells(11, HowMany + 3).Offset(, 1).Resize(, 31 - HowMany)
rng.EntireColumn.Hidden = True

End Sub

Save the workbook as an .xlsm file

Hope that helps, good luck with the project.

SUCCESS!!!, THANKS A LOT MATE. YOU ARE A GENIUS.

 

[nitiona]

Hi Greetings,
I have one more query.

Basically I have excel containing 4 columns, having unique data. I need a formula that can compare data in 4 columns and return only unique values in a new colum with '+' separator, removing any duplicates. Sample screenshot is attached.

 

[RoryA]

You've also cross-posted that one here: VBA code for deleting rows based on number of days in a month - OzGrid Free Excel/VBA Help Forum

This is your last warning to follow the rules on cross-posting. Next time you will be banned.

 

[nitiona]

You've also cross-posted that one here: VBA code for deleting rows based on number of days in a month - OzGrid Free Excel/VBA Help Forum

This is your last warning to follow the rules on cross-posting. Next time you will be banned.

Apologies, my mistake. I will delete it and repost it. Thanks.