CODEWITHSUNDEEP
vbaexcel
Robillard
Robillard

Reputation: 117

Issue trying to filter long numbers

I have a column with lots of longer numbers in it. I want to filter my column for all numbers that DO NOT begin with 2. I tried writing it in code to select all of the 2's first, but that didn't work. Then I tried to do it by hand in Excel by saying filter, custom rule, does not begin with 2. But nothing changes. I don't see, for example, a zero before the numbers either. I tried to reformat them into numbers but they were already numbers. I am not sure why it isn't selecting either all the numbers that start with 2 or all the numbers that DON'T start with 2.

Here is the code I tried to get numbers that START with 2.

With ws1.Range("A1:Z" & UsdRws)
    .autofilter Field:=6, Criteria1:="2*", Operator:=xlFilterValues
    On Error Resume Next

    UsdRws = ws1.Range("C" & Rows.Count).End(xlUp).Row

    If UsdRws > 1 Then
        Range("A2:A" & UsdRws).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    On Error GoTo 0
    .autofilter Field:=9
    Cells.EntireColumn.AutoFit
End With

The filter does not change anything even though I have a lot of numbers beginning with 2. Here is a sample of the numbers I am using.

2190190000
1410650400
1410720000
1410511900
1410650000
1410511900
1410650500
1410680100
1410650300
2190160000
1410650100
2190160000
2020110008
1410511900
1410650400
2020110002
2020180000
2010110100
1410671000
2190190000
1410650200
2020180000
2190190000
1410650400
1410720000
1410511900
1410650000
1410511900
1410650500

So like I said, I am trying to get either all the numbers beginning with 2 or all the other ones, but it seems like neither is working, even when doing it by hand using a custom filter rule.

Upvotes: 0

Views: 232

Answers (3)

CallumDA
CallumDA

Reputation: 12113

This works regardless of how many digits your numbers contain. Your inputRange should just be the column with the long numbers in, include headers too.

Sub Remove2()
    Dim inputRange As Range
    Dim v As Variant
    Dim i As Long, deleteCount As Long, min As Long

    Set inputRange = Sheet1.Range("A1:A10001")
    v = inputRange.Value
    min = Application.WorksheetFunction.min(inputRange) - 1

    For i = 1 To UBound(v)
        If Left(v(i, 1), 1) = "2" Then
            v(i, 1) = min
            deleteCount = deleteCount + 1
        End If
    Next i

    With inputRange
        .Value = v
        .Sort key1:=.Resize(1, 1), order1:=xlAscending, Header:=xlYes 'xlNo if no headers
        .Resize(deleteCount, 1).Offset(1, 0).EntireRow.Delete 'remove offset if no headers
    End With
End Sub

tested with 10,000 numbers taking 0.4 seconds

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

If all of your numbers are 10 digit numbers, then the following seems to work:

Option Explicit
Sub FilterTwos()
    Dim ws1 As Worksheet
    Dim r As Range

Set ws1 = Worksheets("sheet1")
With ws1
    Set r = .Range("a2", Cells(.Rows.Count, "A").End(xlUp))
End With

ws1.AutoFilterMode = False

r.AutoFilter Field:=1, Criteria1:=">=2000000000", Operator:=xlAnd, _
    Criteria2:="<3000000000"

Set r = r.SpecialCells(xlCellTypeVisible)
r.Copy Worksheets("sheet2").Range("B2")

End Sub

Upvotes: 1

Carol
Carol

Reputation: 471

In your With statement you use UsdRws but don't give UsdRws a value until within the 'With'. May this be causing your problem?

You could also try treating the numbers as strings (change format to text?). This way Excel shouldn't get confused by what it thinks you should or shouldn't be able to do with numbers.

Upvotes: 1

Related Questions