change to macro that autofilter's rows based on entered ship date

[steve400243]

Hello, The attached has a working macro, shown below, that filters out rows when a date is entered into Cell "B1" It hides any row that does not match the Ship date in column "E". I would like the macro changed to show all data, regardless of ship date, when the cell B1 is left empty. Thanks for the help!

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

Herban Cowboy Test.xlsm
	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						Each:	$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	2970772	456808572	UNFI - AURORA	Apr-20	Apr-27		1	1	2	2	1			$ 42.35
5	2970773	476811839	UNFI - LANCASTER	Apr-20	Apr-27		1	1	6	2	1			$ 46.55
6	2970776	466789884	UNFI - RIDGEFIELD	Apr-20	Apr-27		1	1	1	2	1			$ 41.30
9	Total													$ 130.20
Data

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

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

 

[kevin9999]

Try replacing your Worksheet_Change code with this:

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

 

[steve400243]

That gives me a Run time error '1004': Method "Showalldata' of object'_worksheet' failed

 

[kevin9999]

That gives me a Run time error '1004': Method "Showalldata' of object'_worksheet' failed

Strange - it worked for me. Try changing this line:

Me.ShowAllData

to this:

ActiveSheet.ShowAllData

 

[steve400243]

Nope, still getting the error - and it highlights that line

ActiveSheet.ShowAllData

 

[kevin9999]

OK, here we go:

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

 

[steve400243]

That did it, Thanks again for the updated code! much appreciated.

 

[kevin9999]

That did it, Thanks again for the updated code! much appreciated.

Glad I could help, and thanks for your patience