1

I'm working on something similar to Macro - delete rows based on date but my date data are in a reverse order, from newest to oldest. When I run the code provided by Dan Wagner it doesn't do anything.

I have code that works if I manually set the month 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.

I tried the code with .Range("B2").Resize(Lastrow - 1).Formula = "=AND(MONTH(A2)<>Month(Date))" but it only deleted my headers.

Is there anything that can change the month(Date) function to number?

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
double-beep
  • 5,031
  • 17
  • 33
  • 41
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
  • 29,101
  • 5
  • 23
  • 50
  • 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