• Extra blank lines (Excel 2000)

    Author
    Topic
    #387885

    I am using Excel as a data source for a mail merge letter. When I run the merge, I get 5 blank letters at the end (i.e., the form text is there, but none of the variable information. scratch When I went to the Excel worksheet, I realized that those 5 blank rows show up when I go to the first cell and select the whole worksheet (I use the keyboard, Ctrl + Alt +End). I have tried deleting these rows, clearing these rows, deleting the cells and letting the cells remaining cells shift to the left, then up from the bottom. I even tried deleting all the blank rows after the data to the end of the worksheet. I also tried setting this as a print area.

    Finally, I copied and pasted the data rows into a new worksheet, but I still have one blank row at the end that gives me a blank letter in the final document. igiveup

    Because of the section breaks I have in the document, deleting the blank page and the next page section break in the final document changes the formatting. Not that I can’t work around that, but I’m frustrated with trying to get rid of these blank lines.

    Why are they showing up as data in the worksheet, even after I delete them? hairout

    I’ve not seen this before, and I use Excel a lot for mail merges. I searched the MS Knowledgebase, but didn’t find anything like this. I usually would search the lounge to see if anyone else has this problem before asking for help, but as we know, it’s not available. sigh

    Thanks for any ideas.

    Tia

    Viewing 0 reply threads
    Author
    Replies
    • #678675

      Excel is a bit owly when keeping track of its’ used range. Does it help if you delete the blank rows below the data, save, close and then reopen the workbook?

      • #678744

        No, it still gives me the extra lines.

        But I have worked around it by naming the range and using the range in the merge document. Just a bit of an extra step, but it’s better than fooling with the section breaks when I run the merge. brickwall

        • #678746

          Make yourself a little clean up macro for when you need to redefine that range name after adding new names, etc…

              Range("A1").Select
              Selection.CurrentRegion.Select
              ActiveWorkbook.Names.Add Name:="data", RefersToR1C1:=Selection
              Range("A1").Select
          
          • #679013

            Instead of using
            Range(“A1″).Select
            Selection.CurrentRegion.Select
            ActiveWorkbook.Names.Add Name:=”data”, RefersToR1C1:=Selection
            Range(“A1”).Select

            ..I use
            [a1].CurrentRegion.Name = “data”

            zeddy

            • #679023

              Be careful using CurrentRegion because an empty column or row in the middle of a data set may cause less data to be selected than intended.

      • #678812

        Hi,

        Just a quick thought…

        Instead of saving & reopening the workbook (after deleting the blank rows), you could try using

        ActiveSheet.UsedRange

        or

        ActiveSheet.UsedRange.Select

        The first example simply resets the ‘xlCellTypeLastCell’ on the active sheet

        The second one selects from cell A1 upto the Last Used Cell on the active sheet

        Khushnood

        • #679022

          Unfortunately ActiveSheet.UsedRange will include empty cells which have been formatted, until the WB is saved, closed and reopened. The named range method that someone else (original poster?) suggested is probably the best route.

    Viewing 0 reply threads
    Reply To: Extra blank lines (Excel 2000)

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

    Your information: