Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Rows where condition is met (Excel 2000)

    I have a spreadsheet that was exported into excel from unix. About every 20 lines it imported the column field names and a few blank lines above and below it. It could be upon exporting from unix the line about supressing field names was not checked, is there a way to remove all blank lines and any lines containing the field names leaving only the other data. For example, on any row that begins with a SSN e.g. 345675432 in Column A to keep that data, and any row that doesn't contain a SSN in Column A to Delete it...know it has to be possible. Maybe a piece of code?

    Thank you,
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deleting Rows where condition is met (Excel 2000)

    I would use Autofilter. Check out this <post#=370992>post 370992</post#> for some details

    Steve

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

    Re: Deleting Rows where condition is met (Excel 2000)

    The easy way would be to sort the sheet on Column A. This should put all of the label and blank rows together where they can be selected and deleted.
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Rows where condition is met (Excel 2000)

    Thank you everyone...Both of these methods seem to be excellent solutions.

    As always I salute you, <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Rows where condition is met (Excel 2000)

    Well It got rid of the blanks, but messed up the original sort of LastName and ClassesTaken, and tried to resort by LastName (Primary) and ClassesTaken (Secondary) again, and it would not sort back to the way it was before getting rid of the completely blank lines because of all the blanks underneath LastName field...Example:

    First Column Holds LName(In this case it is Brown)
    Second Column Holds (Class Taken), such as: FirstAid, Office, Windows
    Then the next persons name in First Column Holds LName(Williams)
    and all the classes he has taken in Second Column like: Access, FirstAid, Office


    Now working this out in an Access report is how I"D do it, but it's not up to me, and has to stay in Excel for the user...I don't know why not.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Rows where condition is met (Excel 2000)

    NMP,

    Can you fill an empty column with sequential numbers and include that in the sort? Then do your Sort, delete rows, and resort based on the column of numbers you added. That is the way I do it when I want to be sure to get the info back into its original order.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deleting Rows where condition is met (Excel 2000)

    An excellent suggestion. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    This is one of the first columns I create when sorting records: A means of returning to the original sort order no matter what.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Rows where condition is met (Excel 2000)

    What an excellent idea...thank you. That should solve it. Will let you know when I return to work tomorrow. Am helping a secretary on this one.

    Will give the kudus to all of you helpful folks if it works out.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Rows where condition is met (Excel 2000)

    That sinched it...thank you for the most helpful information. She met her deadline, and honors to all of you. Thank you,
    NMP <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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