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.
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
however I'm not sure if this part of the code is right
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.
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.
DATE | DAY | MONTH | YEAR | 1st Sun | 1st Mon | 1st Tues | 1st Wed | 1st Thur | 1st Fri |
01/01/2022 | Saturday | January | 2022 | 26 | 27 | 28 | 29 | 30 | 31 |
01/02/2022 | Tuesday | February | 2022 | 30 | 31 | 01 | 02 | 03 | 04 |
01/03/2022 | Tuesday | March | 2022 | 27 | 28 | 01 | 02 | 03 | 04 |
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.