Vba filter, get unique values, print list, delete visible

Scoti 1 Reputation point
2021-07-25T22:14:09.513+00:00

Hello, pretty new to this so bear with me. I have a workbook in which I need to be able to filter the data based on the date(hide all within the last 3 months), then obtain the unique values in column E, print a list of those unique values, then delete the visible(older than 3 months) rows, and unhide the remaining data. This is the code I have been working on however it is not working other than printing. Can anyone help me out with this. I was unable to attach a sample workbook on this device so I am sharing a screenshot.
*Cross posted @ Mr. Excel
VBA Code:
' Terminate_Archived_90 Macro

' This macro deletes all samples older than 90 days from the Archived List and prints a report of all Trays that are to be dumped.

Dim sht As Worksheet

Dim dDate As Object

Set sht = Worksheets("Archived")

Set dDate = sht.Range("M1")

With sht.Range("A1:H" & Range("A" & Rows.Count).End(xlUp).Row)

.AutoFilter  

.AutoFilter Field:=8, Criteria1:=">dDate"  

lastrow = Cells(Rows.Count, "E").End(xlUp).Row

ActiveSheet.Range("E2:E" & lastrow).AdvancedFilter _

Action:=xlFilterCopy, _

CopyToRange:=ActiveSheet.Range("O2"), _

Unique:=True

Range("O1:O" & [O65536].End(xlUp).Row).PrintOut

Range("A2:H" & [h65536].End(xlUp)).Delete shift:=xlUp

Range("A:H").EntireRow.Visible = True

End With

End Sub
117660-screenshot-20210725-064654-excel.jpg

{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.