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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
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,448
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,523
Messages
5,548,547
Members
410,848
Latest member
anuradhagrewal
Top