title: Delete date that falls outside of inputted month
body: <p>I'm creating a sub that formats a new time sheet. I need it to delete dates that fall outside of the inputted month (in this case, June, 2020). The code autofills the next 30 days after the first day, which covers the maximum days in a month (31 days), but also adds date for the first day(s) in the next month if the inputted month has less than 31 days. Here is my code:</p>
  
  <pre><code>Sub Calendar_Genorator3()
  Dim WS As Worksheet
  Dim MyInput As Variant
  Dim StartDay As Variant
  Dim DayofWeek As Variant
  Dim CurYear As Variant
  Dim CurMonth As Variant
  Dim FinalDay As Variant
  Dim Cell As Range
  Dim RowCell As Long
  Dim ColCell As Long
  Dim Day1 As Range
  
  Set WS = ActiveWorkbook.ActiveSheet
  WS.Range("A1:R100").Clear
  
  MyInput = InputBox("Type in Month and year for Calendar ")
      If MyInput = "" Then Exit Sub
         ' Get the date value of the beginning of inputted month.
         StartDay = DateValue(MyInput)
         ' Check if valid date but not the first of the month
         ' -- if so, reset StartDay to first day of month.
         If Day(StartDay) &lt;&gt; 1 Then
             StartDay = DateValue(Month(StartDay) &amp; "/1/" &amp; _
                 Year(StartDay))
         End If
  
         'Set headers
         Range("a1").Value = Application.Text(MyInput, "mmmm") &amp; " Time Sheet"
         Range("a2") = "Day"
         Range("c2") = "Time In"
         Range("d2") = "Time Out"
         Range("e2") = "Hours"
         Range("f2") = "Notes"
         Range("g2") = "Overtime"
  
         'Set weekdays
         Range("a3") = "Sunday"
         Range("a4") = "Monday"
         Range("a5") = "Tuesday"
         Range("a6") = "Wednesday"
         Range("a7") = "Thursday"
         Range("a8") = "Friday"
         Range("a9") = "Saturday"
  
         DayofWeek = Weekday(StartDay)
         ' Set variables to identify the year and month as separate variables.
         CurYear = Year(StartDay)
         CurMonth = Month(StartDay)
         ' Set variable and calculate the first day of the next month.
         FinalDay = DateSerial(CurYear, CurMonth + 1, 1)
         ' Place a "1" in cell position of the first day of the chosen month based on DayofWeek.
         Select Case DayofWeek
             Case 1
                 Range("b3").Value = 1
             Case 2
                 Range("b4").Value = 1
             Case 3
                 Range("b5").Value = 1
             Case 4
                 Range("b6").Value = 1
             Case 5
                 Range("b7").Value = 1
             Case 6
                 Range("b8").Value = 1
             Case 7
                 Range("b9").Value = 1
         End Select
  
  'Loop through range b3:b44 incrementing each cell after the "1" cell.
  For Each Cell In Range("b3:b44")
      RowCell = Cell.Row
      ColCell = Cell.Column
      'Do if "1" is in column B or 2.
          If Cell.Row = 1 And Cell.Column = 2 Then
          ' Do if current cell is not in 1st column.
              ElseIf Cell.Row &lt;&gt; 1 Then
                  If Cell.Offset(-1, 0).Value &gt;= 1 Then
                      Cell.Value = Cell.Offset(-1, 0).Value + 1
                      ' Stop when the last day of the month has been entered.
                      If Cell.Value &gt; (FinalDay - StartDay) Then
                          Cell.Value = ""
                          ' Exit loop when calendar has correct number of days shown.
                          Exit For
                      End If
                  End If
          End If
  Next
  
  For Each Cell In Range("b3:b9")
      If Cell.Value = "" Then
          Cell.EntireRow.Clear
      End If
  Next
  'Clears rows without dates
  
  For Each Cell In Range("b3:b9")
      If Cell.Value = "" Then
          Cell.EntireRow.Delete
      End If
  Next
  'Deletes top rows without dates; needs a loop to successfully delete all empty rows
  
      Range("b2") = "Date"
      'Added "Date" in later so date insert works
  
  Set Day1 = WS.Cells.Find(What:="1", LookIn:=xlValues, _
          lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, _
          MatchCase:=True, SearchFormat:=False)
          'Find start day, which is day 1
          If Not Day1 Is Nothing Then
              Day1.Value = Application.Text(MyInput, "mmm-d")
          End If
  
          With Day1
              Day1.AutoFill Destination:=Range("B3:B33"), Type:=xlFillDefault
          End With
  
  'These final lines of code don't delete ranges with dates that fall outside of the inputted month, because FinalDay doesn't refer to the last day of the month. I need to come up with something that refers to the last day of the month. 
          FinalDay.Select
          With Selection
                  Cell.Offset(-1).End(xlDown).EntireRow.Delete
          End With
  End Sub
  </code></pre>
  
  <p>Here's the output of this code. Any ideas on how to delete the rows that have dates that fall outside of the inputted month? I'm also open to writing this sub an entirely different way; I did it this way because I'm basing it on MS template code. </p>
  
  <p><a href="https://i.stack.imgur.com/fVpEf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fVpEf.png" alt="enter image description here"></a></p>