Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Delete Row if not a Social Security number

I have page after page of data starting with Social Security  number in Column A. I need to delete the page breaks which are blank rows or rows that are not Social Security Numbers.
I am using the ASCii code to recognise the Social Security number .
Dim e As Long
Dmim f As Long

e= Range (A65536,9).End(xlUp).Row
For f = e To 3 Step -1
If Not Range("I" & f) = " & Asc("0") To "Asc("9") Then
Range("A & f & ":" "I" & f).Delete.Shift.xl(Up)

Open in new window

1 Solution
Tommy KinardCommented:
Backup all data before using please.

Sub KillNoSocial()
    Dim e As Long
    Dim f As Long
    Dim ShrtData As String
    e = ActiveSheet.UsedRange.Rows.Count
    For f = e To 3 Step -1
        If InStr(1, Cells(f, 1), "-") = 0 Then
            ShrtData = CStr(Cells(f, 1))
            If Len(ShrtData) <> 9 Or Not IsNumeric(ShrtData) Then
            End If
        End If
End Sub

Open in new window

llawrencegAuthor Commented:
Great Alternative

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now