Remove data in a column if dated next month

[Martin sherk]

How to delete rows based on the date in the column (Due date), if it's dated next month, say if date in that column is dated Jan 23. i want it to be removed.

 

[breynolds0431]

Hi. Give the below a test.

Sub deletenxtmo()

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Dim x As Long

With ws

    'finds last row
    'Set the column (currently G) to column with due dates
    Dim lrow As Long: lrow = .Range("G" & .Rows.Count).End(xlUp).Row
    'Change 7 to the index of the column with due dates (i.e.; G = 7)
    Dim ddCol As Integer: ddCol = 7

    'will work from bottom up and delete rows where due date month or
    'year is greater than current month or year
    For x = lrow To 2 Step -1
        If Month(.Cells(x, ddCol)) > Month(Date) Or Year(.Cells(x, ddCol)) > Year(Date) Then
            .Rows(x).Delete
        End If
    Next x

End With

End Sub

 

[Martin sherk]

Hi. Give the below a test.

Sub deletenxtmo()

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Dim x As Long

With ws

    'finds last row
    'Set the column (currently G) to column with due dates
    Dim lrow As Long: lrow = .Range("G" & .Rows.Count).End(xlUp).Row
    'Change 7 to the index of the column with due dates (i.e.; G = 7)
    Dim ddCol As Integer: ddCol = 7

    'will work from bottom up and delete rows where due date month or
    'year is greater than current month or year
    For x = lrow To 2 Step -1
        If Month(.Cells(x, ddCol)) > Month(Date) Or Year(.Cells(x, ddCol)) > Year(Date) Then
            .Rows(x).Delete
        End If
    Next x

End With

End Sub

perfect solution but i wonder if it can be done in power query ?

 

[GraH]

Look into the date column filter. There are things like this month, next month etc.
Then update the formula like not Date.IsInNextMonth([Custom])

 

[Martin sherk]

Look into the date column filter. There are things like this month, next month etc.
Then update the formula like not Date.IsInNextMonth([Custom])
View attachment 80633

Thank you! is there a way to keep everything from previous months till today and remove anything after this month?

 

[GraH]

would be like

Table.SelectRows(PreviousStep, each ( Date.IsInPreviousMonth( [DateCol] ) or [DateCol] <= Date.From( DateTime.LocalNow() ) ) and not Date.IsInNextMonth( [DateCol] ) )

 

[Martin sherk]

would be like

Table.SelectRows(PreviousStep, each ( Date.IsInPreviousMonth( [DateCol] ) or [DateCol] <= Date.From( DateTime.LocalNow() ) ) and not Date.IsInNextMonth( [DateCol] ) )

I'm sorry, i am new to this. how to add this line to my query?

what comes before the equal sign, what should i type

 

[GraH]

First activate the formula bar in the UI.
Then add a simple filter at that point in your Query where it is needed. Look in formula bar and update the formula like in my example.
else provide your code so we can help you more explicitely.

 

[Martin sherk]

First activate the formula bar in the UI.
Then add a simple filter at that point in your Query where it is needed. Look in formula bar and update the formula like in my example.
else provide your code so we can help you more explicitely.

i got it to work but it's removing data that is greater than today not greater than this month, it removes data that is in December, it removed data dated 16 Dec. and 21 Dec and above.

 

[GraH]

Thank you! is there a way to keep everything from previous months till today and remove anything after this month?

You said till today, so that's why I updated the formula. Just leave out that argument and you are probably good to go, no?