Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete lines at end of file (WINXP Excel 2000)

    I have a macro that does everything I want except:

    Every week I convert a text file to Excel (no problem).

    My macro deletes the first line (and does some other formatting )

    I have to manually delete the "garbage" at the end of the file before I run the macro.
    The reason I have to do it manually, is because the file never has the same number of records..

    Is there code I can put in my macro that says:
    Go to the last record (identified by a blank row) and delete everything below the blank line.

    I appreciate any ideas.

    Thank you,
    Michael

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete lines at end of file (WINXP Excel 2000)

    Assuming Column A is the first column of your data, you could try

    [A1].End(xlDown).Offset(2, 0).CurrentRegion.Delete

    This would require that each record have data in column A

    Andrew C

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete lines at end of file (WINXP Excel 2000)

    EXACTLY what I need. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    Thank you so much Mr. Cronnolly !!

    Michael

    Edited 2 minutes later.......

    What if there is a small square box in Column A (carriage return symbol?)
    Could the above code be modified to look for that symbol and delete everything below?

    Sorry to be a pain <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete lines at end of file (WINXP Excel 2000)

    You are not a pain at all.

    Try

    [A:A].Find(What:=Chr(13)).CurrentRegion.Delete

    Andrew C

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete lines at end of file (WINXP Excel 2000)

    I am receiving Run Time Error 91: Object Variable or With block Variable not set

    Here is the macro:

    Sub deletebottom()
    [A:A].Find(What:=Chr(13)).CurrentRegion.Delete
    End Sub

    The Help File mentioned a possible Missing Reference.

    Can you please assist?

    Thank you (again)
    Michael

  6. #6
    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: Delete lines at end of file (WINXP Excel 2000)

    You would get this error if column A did not have any carriage returns [=char(13)].

    Perhaps the char is not a carriage return. You could copy just the "square" from a cell to another cell (eg A1) and then use
    <pre>=code(a1)</pre>


    to get the char number.

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete lines at end of file (WINXP Excel 2000)

    OK - I'm a bit closer. It turns out to be 12 instead of 13.

    But the code deletes all the records ABOVE the record w/char 12 !! I need to delete the records
    below the char 12 (and the row with the char 12)

    How should I modify the code -

    Sub deletebottom()
    [A:A].Find(What:=Chr(12)).CurrentRegion.Delete
    End Sub

    Thank you very much.

  8. #8
    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: Delete lines at end of file (WINXP Excel 2000)

    This will delete the first row containing a chr(12) and any row below it
    <pre>Sub deletebottom()
    Range([A:A].Find(What:=Chr(12)), Range("A65536")).EntireRow.Delete
    End Sub</pre>


    Steve

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete lines at end of file (WINXP Excel 2000)

    WOW!!!!!!!!

    Thank you Steve - it's EXACTLY what I needed. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    I really appreciate everyone's knowledge and patience here.
    Someday, I hope I'll be able to return the favor(s).

Posting Permissions

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