Delete Row if not a Social Security number

Posted on 2009-12-20
Last Modified: 2013-11-10
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

Question by:llawrenceg
    LVL 14

    Accepted Solution

    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


    Author Closing Comment

    Great Alternative

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This is an explanation of a simple data model to help parse a JSON feed
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now