1

first, apology for my english. Im working on something very similar to post Macro - delete rows based on date

but my date data are in a reverse order, it sort from newest to oldest.

The code provided by Dan Wagner didnt work out for me, as when I run the code it didnt do anything.

I have got a code here that work fine if i manually set the month to be less than a value. .Range("B2").Resize(Lastrow - 1).Formula = "=AND(MONTH(A2)<>4)"

I want the code to delete rows if the month of the date in column A is not the current month. So i tried the code with .Range("B2").Resize(Lastrow - 1).Formula = "=AND(MONTH(A2)<>Month(Date))" but it only deleted my headers.

So i want to know if there is anything that can change the month(Date) function to number? or if there is any other best way of doing this? see my full code below

Sub ProcessData2()
    Dim rng As Range
    Dim Lastrow As Long
    Dim i As Long

    With ActiveSheet

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Columns(2).Insert
        .Range("B1").Value = "tmp"
        .Range("B2").Resize(Lastrow - 1).Formula = "=AND(MONTH(A2)<>Month(Date))"
        Set rng = .Range("A1").Resize(Lastrow, 2)
        rng.AutoFilter Field:=2, Criteria1:="=TRUE"
        On Error Resume Next
        Set rng = .Range("B2").Resize(Lastrow - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rng Is Nothing Then rng.EntireRow.Delete
        .Columns(2).Delete
    End With
End Sub
Community
  • 1
  • 1
rei123
  • 63
  • 1
  • 1
  • 11

1 Answers1

0

Looks ok, just try changing your formula into this:

.Range("B2").Resize(Lastrow - 1).Formula = "=MONTH(A2)<>MONTH(TODAY())"
A.S.H
  • 28,723
  • 5
  • 21
  • 48
  • 1
    Thank you! I actually have tried with the code, and it wasnt working, and your reply just made me double checked my code, and found there was ")" missing!! now its working fine!! :) – rei123 Apr 19 '17 at 09:55
  • @rei123 you are welcome. Good to know it is fixed :) – A.S.H Apr 19 '17 at 09:57