Excel VBA: how to create an auto filter with filter exception?
I am new to VBA and am working on a project where I have to filter data between two specific values (4400000000 and 5600000000). Where it gets tricky for me is that some of the 5.5 billion have letters behind them i.e. 5500000000 CST. How can I write my code to include these numbers if there are letters behind them. If this is too vague, how can I write my code to include CST? Here is my code:
Sub macro5()
Worksheets("info1").Range("A1").AutoFilter _
Field:=7, _
Criteria1:=">=" & 4400000000#, Operator:=xlFilterValues, Criteria2:="<5600000000, Operator:=xlFilterValues"
End Sub
+3
source to share
1 answer
Loop through and create a dictionary of values to filter, then use keys as criteria1 with xlfiltervalues.
Option Explicit
Sub macro5()
Dim d As Long, dict As Object
Dim i As Double, mn As Double, mx As Double
Set dict = CreateObject("scripting.dictionary")
mn = 4400000000#
mx = 5600000000#
With Worksheets("info1")
If .AutoFilterMode Then .AutoFilterMode = False
For d = 2 To .Cells(.Rows.Count, "G").End(xlUp).Row
If IsNumeric(Left(.Cells(d, "G").Value2, 1)) Then
i = CDbl(Split(.Cells(d, "G").Value2 & Chr(32), Chr(32))(0))
If i >= mn And i < mx Then
dict.Item(CStr(.Cells(d, "G").Value2)) = .Cells(d, "G").Value2
End If
End If
Next d
.Range("A1").AutoFilter Field:=7, Criteria1:=dict.keys, Operator:=xlFilterValues
End With
End Sub
+2
source to share