VBA delete days outwith month

EMcK01

Board Regular
Joined
Jun 14, 2015
Messages
114
Hi,

This is a follow on to something that I asked recently as I hadn't intended in creating a macro for.

I have a table of dates that I use to pull into another programme, this is a part of it but it covers all the months in the year and extends to 46 columns of data.

DATEDAYMONTHYEAR1st Sun1st Mon1st Tues1st Wed1st Thur1st Fri
01/01/2022SaturdayJanuary2022262728293031
01/02/2022TuesdayFebruary2022303101020304
01/03/2022TuesdayMarch2022272801020304

Currently it highlights the dates that are outwith that month using the following conditional formatting =MONTH(E2)<>MONTH($A2)
Then I can then manually delete, ie those that are bold I would delete before importing.

I was hoping there was a way that I could delete those dates that are outwith the month thats shown in column A.

I was trying something like this
VBA Code:
Sub DeleteDays()
    Dim nRow As Long, nCol As Long
    
    nRow = Cells(Rows.Count, "A").End(xlUp).Row
    nCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    i = 1
    j = 4
    
    Do
    i = i + 1
    j = j + 1
    
    Set RngA = Range(Cells(i, j), Cells(nRow, nCol))
    
        For Each cell In RngA
              If cell.Value.Month <> Range(Cells(i, 1)).Month Then
              cell.ClearContents
              
            End If
        Next cell

    Loop Until Range(Cells(nRow, nCol))
End Sub

however I'm not sure if this part of the code is right
Code:
If cell.Value.Month <> Range(Cells(i, 1)).Month Then

              cell.ClearContents

or if it is set up to properly loop through the table to clear the contents correctly.

For info the cells in column A and from column E onwards are all formatted as dates, with those from E onwards showing the day only.

Any thoughts on this would help.

Thanks.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

BSALV

Well-known Member
Joined
Oct 31, 2010
Messages
1,470
Office Version
  1. 365
  2. 2013
  3. 2007
VBA Code:
Sub DeleteDays()
     Dim i, j
     For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
          For j = 4 To Cells(1, Columns.Count).End(xlToLeft).Column
               If Month(Cells(i, j).Value) <> Month(Cells(i, 1).Value) Then Cells(i, j).ClearContents
          Next
     Next
End Sub
 
Solution

EMcK01

Board Regular
Joined
Jun 14, 2015
Messages
114
Ideal, thank you. A lot cleaner than my code as well!
Appreciate you looking at this for me.
Thanks.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,268
Messages
5,836,301
Members
430,419
Latest member
silvaleroy

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
Top