show all data in filtered data if value cell is empty

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
848
Office Version
  1. 2010
i have this code by filtered data based on cell a5 i try show all data if cell a5="" but it gives me error how can i fixed
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 And Target.Column = 1 Then
If Target.Address = Range("a5").Address = "" Then
sheet1.ShowAllData
    sheet1.Cells.AutoFilter Field:=5, Criteria1:=sheet1.Range("a5").Value
End If
End If
End Sub
thanks
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 And Target.Column = 1 Then
   If Target.Value = "" Then
      Sheet1.ShowAllData
   Else
       Sheet1.Cells.AutoFilter Field:=5, Criteria1:=Target.Value
   End If
End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,587
Messages
5,548,889
Members
410,883
Latest member
pinch
Top