0

I need some help with some VBA Excel code to format a spreadsheet please.

I have to run a report that give me an excel spreadsheet which has an invoice date in column P. I need to delete the entire row if the invoice date in the cell in column P is less than or equal to 60 days from todays date, and also delete any row that has an invoice date in column P of 4 years or more from todays date.

I normally have to do this manually each time I run the report so I would like to automate the process with some VBA code. Can anyone help me with this please.

Dim x As Long
For x = [a1].SpecialCells(xlCellTypeLastCell).row To 1 Step -1
    Debug.Print Cells(x, "P").Value
    If CDate(Cells(x, "P")) > Date - 60 Then
        Cells(x, "P").EntireRow.Delete
         Else
         Exit Sub
    End If
Next x

I got the above code to work

Community
  • 1
  • 1
ianf
  • 39
  • 9
  • 1
    Have you used VBA before? Have you tried to use VBA here? Please show us what you have tried. Take a look here for introductory help with your question: http://stackoverflow.com/questions/7648655/how-to-delete-rows-in-excel-based-on-criteria-using-vba – Grade 'Eh' Bacon Jul 23 '15 at 13:24
  • Here is a link to a page about "If" statements, one for deleting rows, and one for loops. You should just need these three things to put together a basic program that does what you want. [1]: http://www.techonthenet.com/excel/formulas/if_then.php [2]: http://stackoverflow.com/questions/7851859/delete-a-row-in-excel-vba [3]: https://msdn.microsoft.com/en-us/library/eked04a7.aspx – User30923 Jul 23 '15 at 13:39

1 Answers1

-1
Dim LastRow as Integer
Dim row as Integer

LastRow = ActiveSheet.UsedRange.Rows.Count    

For row = 2 To LastRow
    If Cells(row, 16).Value < Date - 59 OR Cells(row, 16).Value > Year(Cells(row, 16)) + 4 Then
        Rows(row).Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp
    End If
Next row

I had this more or less lying around, and tweaked it a bit. Let me know if this works for you.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
  • Oh I forgot the second part of your question. Will work on that quickly. – Luuklag Jul 23 '15 at 14:57
  • Thanks I tried the above however I could not get it to work. It is having issues with the statement If (Cells(row, 16)).Value < Date - 59 OR (Cells(row, 16)).Value > Year(Cells(row, 16)) + 4 Then – ianf Jul 25 '15 at 13:24
  • I ran the code myself, and found out I put some brackets in the wrong place. Updated my answer. For me it works now. – Luuklag Jul 27 '15 at 07:01