News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Saving TXT file via VBA and blank rows are being inserted

    Posted on krweaver Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Saving TXT file via VBA and blank rows are being inserted

    Viewing 3 reply threads
    • Author
      • #934103 Reply
        AskWoody Lounger

        I am saving a sheet as a txt file using a VBA save routine.

        Before saving the file, the sheet has been formatted and has ONLY 6 rows.

        After saving, the txt file results with the 6 rows perfectly formatted, but the txt file has 9 rows with the last three displaying 4 commas in each row.

        What could possibly be going on?

      • #941353 Reply
        Paul T
        AskWoody MVP

        I suspect there is some formatting in the last 3 rows that your macro picks up.

        Do you select the region to save of is it Save As, Text?

        cheers, Paul

      • #958666 Reply
        AskWoody Lounger

        I thought I selected the valid region with this:


        And, if I suspend the code, the region is, in fact, selected (6 rows, 5 columns).

        However, when I continue with the save routine, I still get those add’l 3 lines that theoretically were and should have been deleted (through a filter, then delete visible). The resulting txt file still has 3 lines at the bottom with all commas there.

      • #967296 Reply
        AskWoody Lounger

        I got a work-around…maybe not elegant, but it works and that’s a huge part of the programming battle. I tested the sheet to find the first blank cell, then deleted from that cell down a reasonable number of rows that I anticipate won’t ever be in this sheet. In addition to deleting them, I also did a “clear” on that range to be sure the format didn’t return to haunt me. Seems to work on a few files I tested. Whew!

        1 user thanked author for this post.
    Viewing 3 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Saving TXT file via VBA and blank rows are being inserted

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.