Filter based on active cell vba code

[anna99]

Hi all, could you please help me. i'm trying to auto filter based on active cell, however, when i click onto empty cell, it will show an error. how do i fix this. my code curretnly as below and work well except for empty cell. i want to keep under worksheet so it can auto run

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value

End Sub

 

[Osvaldo Palmeiro]

Hi, Anna.
Please, try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Value = "" Then Exit Sub
 Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End Sub

 

[anna99]

Hi, Anna.
Please, try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = "" Then Exit Sub
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End Sub

Thank you so much Osvaldo

 

[anna99]

Hi, Anna.
Please, try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = "" Then Exit Sub
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End Sub

Hi Osvaldo, this works for empty cells, could you advise how i can remove the error/ debug if i choose the whole column, so if more than 1 cells chosen --> then exit sub, thanks for your help

 

[Fluff]

Please do not put your response inside the quote, as it looks as though you have simply quoted a post without saying anything. I have change it for you this time.

 

[Osvaldo Palmeiro]

Hi, Anna.
Try this instead.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Count > 1 Then Exit Sub
 If Target.Value = "" Then Exit Sub
 Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End Sub

 

[anna99]

Hi, Anna.
Try this instead.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End Sub

Perfect, appreciate your help. just 1 last thing, could you please help to limit filter based on active cell values of Column A only instead of a whole worksheet.

 

[Osvaldo Palmeiro]

Here you go.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Count > 1 Then Exit Sub
 If Target.Column > 1 Or Target.Value = "" Then Exit Sub
 Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End Sub