Help with AutoFilter using VBA

[David Lipetz]

Folks,

I am completely new to the whole Excel macro/VBA world, though I am pretty
spiffy when in comes to Excel.

By using lots of Google, I have put together the VBA macro below that
actually does work. However, I would like to add code so that the AutoFilter
custom parameters are set by default to: Custom, Is Greater Than, 0.

How can I accomplish this within this code:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C7"), .Cells) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect
With .Offset(0, 2)
.NumberFormat = "mmmm d, yyyy"
.Value = Date
End With
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True
Application.EnableEvents = True
End If
End With
End Sub

Thanks in advance.

David

 

[Dave Peterson]

I'm not sure you can change those defaults.

You can filter on a field (or multiple fields) using those parms, though.

 

[David Lipetz]

Isn't there a way to tell AutoFilter what criteria to use by using the
Selection.Autofilter command?

My goal is when AutoFilter/Custom is selected by the user, it is preset as
described below (is greater than... 0)

No way to do this?

 

[Dave Peterson]

Record a macro when you apply the filter and then filter by the field that you
want.

But that actually applies the filter--it doesn't make it the default.

 

[David Lipetz]

Thx. Perhaps I am asking the wrong question.

Being a VBA neophyte, I am having trouble understanding how to use the
Selection.Autofilter command. Seems as though that will do what I want, but
I just don't understand how to use it properly. I'll keep digging.

 

[Dave Peterson]

Did you look at the macro you recorded?

If you need help understanding what it did, include it in your followup post
with your question.

Thx. Perhaps I am asking the wrong question.

Being a VBA neophyte, I am having trouble understanding how to use the
Selection.Autofilter command. Seems as though that will do what I want, but
I just don't understand how to use it properly. I'll keep digging.