[SOLVED] Clear Formula from non-active cells vba Excel

Clear Formula from non-active cells vba Excel

I have Combobox in userform for 12 months. combobox populate dates in rows according to month. I select "January" and I have 31 dates in rows. I am trying to clear the formula (with every change of month from the rest of the rows when I select "February" (28 dates) or any month which have less than 31 dates.

The code I used is below :

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Month")

With Me.cmb_mnth
    .Clear
    .AddItem "January"
    .AddItem "February"
    .AddItem "March"
    .AddItem "April"
    .AddItem "May"
    .AddItem "June"
    .AddItem "July"
    .AddItem "August"
    .AddItem "September"
    .AddItem "October"
    .AddItem "November"
    .AddItem "December"
    .ListIndex = 9
    End With
    End Sub

   Private Sub cmb_mnth_Change()
   Dim sh As Worksheet
   Set sh = Worksheets("Month")
   sh.Range("B2").Value = Me.cmb_mnth.Value

    sh.Range("A1:A31").Value = "=IFERROR(TEXT(RC[3],""dddd""),"""")"
    sh.Range("D1:D31").Value = "=IFERROR(IF(ROWS(R1C:RC)>DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(R2C2&1),1),0)),"""",DATE(YEAR(TODAY()),MONTH(R2C2&1),ROW(Sheet4!RC[2]))),"""")"
    sh.Range("E1:E31").Value = "=IFERROR(DAY(RC[-1]),"""")"

   End Sub

Solution

First clear the range like FaneDuru showed you then get the correct daycount with:

Public Function GetDayCountOfMonth(year As Long, month As Long) As Long
GetDayCountOfMonth = Day(DateSerial(year, month + 1, 0))
End Function

you would call it with

x = GetDayCountOfMonth(2022,cmb_mnth.listindex + 1) ' 2022 yor year

And then you fill the range accordingly

sh.Range("A1:A" & x).Value ' that is not really best better with cells but ...

Or you go the other way and fill the range like bevor and clear the range 31-x rows