3

Is there a way we can trigger a macro function on column filter in excel??

Please help

Thanks.

Community
  • 1
  • 1
user2144293
  • 213
  • 4
  • 10
  • 17
  • 1
    no there isn't, but if you tell us more about what you want to do we can help – Our Man in Bananas Apr 09 '13 at 14:20
  • @user2144293, do you mean 'user-defined-function' or 'sub macro'? – Kazimierz Jawor Apr 09 '13 at 14:23
  • I need to check if columns filtered has data in those or null. – user2144293 Apr 09 '13 at 14:31
  • 1
    That is very easy :) Use offset.Specialcells to check if it has any rows. See this link http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s – Siddharth Rout Apr 09 '13 at 14:38
  • Have to trigger as soon as I filter. – user2144293 Apr 09 '13 at 14:44
  • 2
    There is no direct way to capture it like @Philip mentioned. You can use the Worksheet_Change Event but then there are many loop holes in that. I have an alternative to suggest. Do not let the user directly filter the data in the sheet but pop a userform from where they can filter the column. You can then from there check if the autofilter produced any results or not – Siddharth Rout Apr 09 '13 at 14:50

1 Answers1

11

I was just thinking if I can post this answer. I guess some of you will not like it as it is not direct answer by presentation of bypass solution. However I think I can show that idea as we don't have all project assumptions in the question.

Let's agree- we all know that there is no event which fires after we change filtering. However, I see one option.

Changing filter could fire Worksheet_Calculate event (not Worksheet_Change). If there is any single formula within your sheet than we will fire that event each time we change filtering criteria using our mouse.

Step 1. put any single formula in the sheet, like in cell ZZ1 where =ZZ2

Step 2. I assume that our data range starts in Range(A1) and we have titles in first row (see the picture). I assume also there is nothing below that area.

enter image description here

Step 3. Put that following solution in Sheet1 module.

Private Sub Worksheet_Calculate()

If ActiveSheet.Name = "Sheet1" Then
    If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
        MsgBox "No data available"
    Else
        MsgBox "There are filtering results"
    End If
End If
End Sub

Step 4. Using filter would fire that event and result with following situations:

enter image description hereenter image description here

I hope someone will like it and can use that. Even if it's only a bypass idea.

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55