VBA filter, get unique values, print, & delete visible

Scoti

New Member
Joined
Nov 8, 2020
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
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.

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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,143,620
Messages
5,719,788
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top