Learn how to a build a cloud-first strategyRegister Now

x
  • 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

0
llawrenceg
Asked:
llawrenceg
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
                ActiveSheet.Rows(f).Delete
            End If
        End If
    Next
End Sub

Open in new window

0
 
llawrencegAuthor Commented:
Great Alternative
0

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