CODEWITHSUNDEEP
vbaexcel
CeFu
CeFu

Reputation: 149

Filtering out Area codes in Excel by VBA

need some assistance. It's been a very long time since I've done scripting/coding and really rusty now.

Issue: I have a spreadsheet(Excel Visual Basic) that contains call(s) over a month(Over 30k records). My goal is to filter out calls that have specific area codes (toll free, local calls...etc) and calculate how much they owe from calling long distance.

Additionally, this is a retirement home and the end-user(s) do not enter phone numbers correctly. I need to either strip 3-4 digits at the start of numbers..

Example: 1800-XXX-XXXX or 800-XXX-XXXX | 1605-XXX-XXXX

Here's the code I currently have, I'm lost on how to incorporate the 3-4 digits in the area and have it parse each record in Column H by the first 3-4 digits

Phone numbers are stored in Column H

Array TFL will store all the area codes I need to filter out.

Sub CleanEntry()
Dim i As Integer
Dim TFL As Variant
TFL = Array("1800", "1877")

For i = Sheet1.UsedRange.Rows.Count To 1 Step -1

If Left(Cells(i, "H"), 4) Like TFL(i) Then
Sheet1.Rows(i).EntireRow.Delete
End If

Next

End Sub

Upvotes: 0

Views: 138

Answers (1)

Comintern
Comintern

Reputation: 22205

I'd personally use a regular expression for this:

Sub CleanEntry()
    Dim i As Integer

    Dim filter As New RegExp
    filter.Pattern = "^1?(8(77|00))|605" 'Or whatever else you need to match.

    For i = Sheet1.UsedRange.Rows.Count To 1 Step -1
        If filter.Test(Trim$(Cells(i, "H").Value)) Then
            Sheet1.Rows(i).EntireRow.Delete
        End If
    Next
End Sub

Note: You'll need to add a reference to Microsoft VBScript Regular Expressions 5.5.

Upvotes: 1

Related Questions