filter out rows on a sheet based on user selected date.

[steve400243]

Hello, On the attached workbook, when a user selects a date in cell B1, I need any other rows that does not have that Date in Column E to be filtered out. In the Example, the Row 4, and 5 would be hidden after entering 4/27 in cell B1. Hope this explains what I am Looking for. Thank you.

Herban Cowboy Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K			N
1	Ship Date	Wed.Apr.27.2022					cc:426	cc:025	cc:426	cc:430	cc:473
2							$5.25	$12.50	$1.05	$0.25	$22.00
3	Order Number	Purchase Order	Destination	Order Date	Ship Date	Carrier	Order Charge	BOL Fee	Carton Out Charge	FedEx Labels	FedEx Pallet			Weekly Charges
4	2970774	416463047	UNFI - ROCKLIN	Apr-20	Apr-20	UNFI	1	1	1	2	1			$ 41.30
5	2970772	456808572	UNFI - AURORA	Apr-20	Apr-20	UNFI	1	1	2	2	1			$ 42.35
6	2970773	476811839	UNFI - LANCASTER	Apr-20	Apr-27	UNFI	1	1	6	2	1			$ 46.55
7	2970776	466789884	UNFI - RIDGEFIELD	Apr-20	Apr-27	UNFI	1	1	1	2	1			$ 41.30
8	2970775	446554404	UNFI - RIVERSIDE	Apr-20	Apr-27	UNFI	1	1	10	2	1			$ 50.75
9	2970767	107210	THRESHOLD ENT	Apr-20	Apr-27	FEDEX FRT	1	1	19		1			$ 59.70
10	Total													$ 281.95
Data

Cell Formulas
Range	Formula
N4:N9	N4	=[Order Charge]*$G$2+[BOL Fee]*$H$2+[Carton Out Charge]*$I$2+[FedEx Labels]*$J$2+[FedEx Pallet]*$K$2
N10	N10	=SUBTOTAL(109,[Weekly Charges])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A9	Cell Value	duplicates	text	NO

 

[kevin9999]

Try the following worksheet change code in the worksheet code area of the Data sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1"), Target) Is Nothing Then
    Application.EnableEvents = False
        With Range("A3:E3")
            .AutoFilter 5, Format(Range("B1").Value, "mmm-dd")
        End With
    End If
    Application.EnableEvents = True
End Sub

 

[steve400243]

Try the following worksheet change code in the worksheet code area of the Data sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1"), Target) Is Nothing Then
    Application.EnableEvents = False
        With Range("A3:E3")
            .AutoFilter 5, Format(Range("B1").Value, "mmm-dd")
        End With
    End If
    Application.EnableEvents = True
End Sub

Kevin9999, Thanks for your code, But I cannot get this to run after pasting the code in the worksheet?

 

[kevin9999]

Kevin9999, Thanks for your code, But I cannot get this to run after pasting the code in the worksheet?

Did you right-click on the sheet tab name, select 'view code', and paste the code in the windows that appeared? If so, the code should run whenever the value changes in cell B1.

 

[steve400243]

Did you right-click on the sheet tab name, select 'view code', and paste the code in the windows that appeared? If so, the code should run whenever the value changes in cell B1.

Ahhhh, need more coffee, I copied and pasted it after hitting alt F11. Works as needed now, thank you!

 

[kevin9999]

Ahhhh, need more coffee, I copied and pasted it after hitting alt F11. Works as needed now, thank you!

Glad to hear and thanks for the feedback.