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

N

nitiona

Banned User
Joined
Jul 29, 2023
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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
 

Attachments

  • Screenshot 2023-07-29 155143.png
    Screenshot 2023-07-29 155143.png
    6.1 KB · Views: 27

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
For understanding purpose, I have attached the detailed screenshot.
 

Attachments

  • Screenshot 2023-07-29 160220.png
    Screenshot 2023-07-29 160220.png
    38.8 KB · Views: 54
Upvote 0
Something like this?
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1MonthFEB
2Year2024
3First Day of Month2/1/2024
4Last Day of Month2/29/2024
5
6Sr NameERP No1234567891011121314151617181920212223242526272829  ####
7THUSATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRI  ####
Sheet3
Cell Formulas
RangeFormula
C3C3=DATEVALUE(C1&C2)
C4C4=EOMONTH(DATEVALUE(C1&C2),0)
D6D6=1
E6:AE6E6=D6+1
AF6AF6=IF($AE6+1>DAY($C4),"",$AE6+1)
AG6AG6=IF($AE6+2>DAY($C4),"",$AE6+2)
AH6AH6=IF($AE6+3>DAY($C4),"",$AE6+3)
D7D7=CHOOSE(WEEKDAY($C$3),"SUN","MON","TUE","WED","THU","FRI","SAT")
E7:AE7E7=CHOOSE(WEEKDAY($C$3+COLUMN()-3),"SUN","MON","TUE","WED","THU","FRI","SAT")
AF7:AH7AF7=IF(LEN(AF6)>0,CHOOSE(WEEKDAY($C$3+COLUMN()-3),"SUN","MON","TUE","WED","THU","FRI","SAT"),"")
Cells with Data Validation
CellAllowCriteria
C1ListJAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
 
Upvote 0
Bosquedeguate said:
Something like this?
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1MonthFEB
2Year2024
3First Day of Month2/1/2024
4Last Day of Month2/29/2024
5
6Sr NameERP No1234567891011121314151617181920212223242526272829  ####
7THUSATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRI  ####
Sheet3
Cell Formulas
RangeFormula
C3C3=DATEVALUE(C1&C2)
C4C4=EOMONTH(DATEVALUE(C1&C2),0)
D6D6=1
E6:AE6E6=D6+1
AF6AF6=IF($AE6+1>DAY($C4),"",$AE6+1)
AG6AG6=IF($AE6+2>DAY($C4),"",$AE6+2)
AH6AH6=IF($AE6+3>DAY($C4),"",$AE6+3)
D7D7=CHOOSE(WEEKDAY($C$3),"SUN","MON","TUE","WED","THU","FRI","SAT")
E7:AE7E7=CHOOSE(WEEKDAY($C$3+COLUMN()-3),"SUN","MON","TUE","WED","THU","FRI","SAT")
AF7:AH7AF7=IF(LEN(AF6)>0,CHOOSE(WEEKDAY($C$3+COLUMN()-3),"SUN","MON","TUE","WED","THU","FRI","SAT"),"")
Cells with Data Validation
CellAllowCriteria
C1ListJAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
Click to expand...
Hi, thanks for your quick response. Yes it clears out the repetition of dates/day based on month (AF-AH), however it doesn't delete the rows. Request you to let me know if any code which can delete these rows based on the month selected.
 
Upvote 0
Delete what rows? None of your examples included rows of data with dates. Your original Post said you wanted to "get rid" of rows, but there was not explanation where these rows are
(are they on the same sheet, different sheet, different workbook, ...). What column is the "date" in for the rows that are to be deleted.

Please provide a more complete set of sample data. Upload sample mini-sheets of sample data via XL2BB
 
Upvote 0
Assuming that delete the rows really means delete the columns
this should do it
VBA Code: 
Sub Only_Days_Of_The_Month()
    
    Dim HowMany As Long
    Dim rng As Range
    
'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.Delete

End Sub

But if you're going to use VBA to fix things why not use VBA to put the dates in to start with?
based on your C7 and C8 cells
VBA Code: 
Sub PutInDates()
    Dim startdate As Date, printdate As Date
    Dim j As Long, col As Long
    
'  first date
startdate = Range("C7").Value

' clear cells that may receive dates
Range("D11:AH12").ClearContents
' write in dates
col = 4
j = 0
With Range("11:11")
    Do
        printdate = startdate + j
        .Cells(, col + j).Resize(2) = printdate
        j = j + 1
    Loop Until printdate = Range("C8").Value
End With

End Sub
 
Upvote 0
NoSparks said:
Assuming that delete the rows really means delete the columns
this should do it
VBA Code: 
Sub Only_Days_Of_The_Month()
   
    Dim HowMany As Long
    Dim rng As Range
   
'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.Delete

End Sub

But if you're going to use VBA to fix things why not use VBA to put the dates in to start with?
based on your C7 and C8 cells
VBA Code: 
Sub PutInDates()
    Dim startdate As Date, printdate As Date
    Dim j As Long, col As Long
   
'  first date
startdate = Range("C7").Value

' clear cells that may receive dates
Range("D11:AH12").ClearContents
' write in dates
col = 4
j = 0
With Range("11:11")
    Do
        printdate = startdate + j
        .Cells(, col + j).Resize(2) = printdate
        j = j + 1
    Loop Until printdate = Range("C8").Value
End With

End Sub
Click to expand...
Hi Thanks for your quick responses.
Actually I was trying to run both the programs but no success. the dates and day row returns with date. I was able to modify it but it does not change automatically when I select different month or year. May be if i'm missing something?
Sorry I'm actually new to VBA. I have enabled macros in options and also below method;
Click Developer > Visual Basic. In the VBA Project Explorer on the left hand side, expand the VBA Project folder for your workbook, then double-click the ThisWorkbook module. Paste your recorded code
but the dates do not change
 
Upvote 0
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.
VBA Code: 
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.
 
Upvote 2
Solution
You must log in or register to reply here.

Similar threads

H
  • Question Question
Formula to identify the number of days per month (incl & excl UK bank holidays).
Replies
3
Views
265
Scott R
S
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
666
Peter_SSs
Peter_SSs
P
  • Question Question
Summing Data based on a count of prior month data
2
Replies
12
Views
1K
KRice
K
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
K
  • Solved
conditional formatting (or VBA?) to go to a specific row number based on the current date
Replies
4
Views
207
kbishop94
K

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top