question

Scoti-5794 avatar image
0 Votes"
Scoti-5794 asked JennyYan-MSFT edited

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

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


office-vba-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Noticed your problem is more related to VBA programing failure in Excel, i changed tag to Office-VBA-dev, in which discusses theTechnical questions about VBA programming in Office.

In the meanwhile, it is also recommended to check the support methods listed in below link for more advices.

Office VBA support and feedback
https://docs.microsoft.com/en-us/office/vba/articles/feedback-support

Best Regards,
Jenny

0 Votes 0 ·

0 Answers