Home » Forums » Open Source Learning Center » Question and Answer

VBA code to filter on the Activecell regardless of cell type (date, text, amount, etc)

Submitted by ND Pard on 13 April, 2014 - 21:58

I need help writing VBA code to filter on the Activecell.

Currently it works except if the activecell is a Date!

I need the code to work regardless of whether the activecell is a date, amount, text, etc.

Basically the data being filtered is a database table that is in Excel.

Any help would be appreciated. Thank you.

‹ randomize values in a range and insert it into another range VBA code to autofilter on activecell with varying cell types: text, number or dates ›
»

This helps, but am not sure

Submitted by ND Pard on 13 April, 2014 - 23:12.

This helps, but am not sure if it will suffice?

Sub Filter_by_Active_Cell()
Dim ColNum As Integer
ColNum = ActiveCell.Column - (ActiveCell.CurrentRegion.Column - 1)
If ActiveCell.NumberFormat = "general" Then
Selection.AutoFilter Field:=ColNum, Criteria1:=ActiveCell
Else

Selection.AutoFilter Field:=ColNum, Criteria1:=Format(ActiveCell, ActiveCell.NumberFormat)
End If
End Sub

»