Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Rows not SS# (Excel 2003)

    I export data from a mainframe to excel and need to analyze the data. However the export includes several page breaks and headers depending upon how much data is exported. I would like to convert the following VBA to delete all rows after row 8 that do not contain a Social security Number or are blank. Can you assist?

    Sub DeleteRowSSN()
    Dim a As Long
    Dim b As Long
    Application.ScreenUpdating = False
    a = Range("A65536").End(xlUp).Row
    For b = a To 8 Step -1
    If IsNumeric(Range("a" & [img]/forums/images/smilies/cool.gif[/img]) Or Range("a" & [img]/forums/images/smilies/cool.gif[/img] = "" Then
    Range("a" & [img]/forums/images/smilies/cool.gif[/img].EntireRow.Delete
    End If
    Next b
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Rows not SS# (Excel 2003)

    What exactly do the SSN numbers look like?

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows not SS# (Excel 2003)

    SS numbers normally look like 999-99-9999. If they are entered like that, then they will be text values not numbers. Or, they could be entered as numbers and formatted to look like that. We would need to know exactly what the SS numbers are in the worksheet. Could you attach a sample workbook that shows the SS numbers, with the numbers altered to not be valid SS numbers?
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows not SS# (Excel 2003)

    Attached is a short representative example ( not exactly the same) for proprietary reasons and to keep my job. I checked the formatting and it was formatted as General so I do not believe that helps. I think you are correct Legare about it being text and if so, it makes it a little more difficult to not delete the SSN text rows yet delete the page headers that are text.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Rows not SS# (Excel 2003)

    Try this:

    Sub DeleteRowSSN()
    Dim a As Long
    Dim b As Long
    Application.ScreenUpdating = False
    a = Range("A65536").End(xlUp).Row
    For b = a To 8 Step -1
    If Range("A" & [img]/forums/images/smilies/cool.gif[/img] = "" Then
    Range("A" & [img]/forums/images/smilies/cool.gif[/img].EntireRow.Delete
    ElseIf Application.WorksheetFunction.IsText(Range("A" & [img]/forums/images/smilies/cool.gif[/img]) And _
    (Asc(Range("A" & [img]/forums/images/smilies/cool.gif[/img]) < 48 Or Asc(Range("A" & [img]/forums/images/smilies/cool.gif[/img]) > 57) Then
    Range("A" & [img]/forums/images/smilies/cool.gif[/img].EntireRow.Delete
    End If
    Next b
    End Sub

  6. #6
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows not SS# (Excel 2003)

    Hans:
    Beautiful work as always. Thank you. However, one additional item has come up. On some of the exports to Excel, there is a date in Column A that prevents the line from being deleted. Can you tweak the code to also delete lines with a date in any format or if that's too difficult to delete the row if the date is in date format of "*00/00/0000" ?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Rows not SS# (Excel 2003)

    Does it work if you change the line
    <code>
    If Range("A" & [img]/forums/images/smilies/cool.gif[/img] = "" Then
    </code>
    to
    <code>
    If Range("A" & [img]/forums/images/smilies/cool.gif[/img] = "" Or IsDate(Range("A" & [img]/forums/images/smilies/cool.gif[/img]) Then
    </code>
    (Always test on a copy of the data!)

  8. #8
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows not SS# (Excel 2003)

    Hans:
    It work great.
    Date Rows are deleted, Text rows (not SSN) are deletd, and blank rows are deleted. I looked up the ARC function you used for the SS# , WOW !! what a tool.
    The data is clean now and I can work on it. The Code saves me probably 15-20 minutes a day so your efforts are greatly appreciated.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Rows not SS# (Excel 2003)

    You're welcome. I'm glad it works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •