Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Hillsboro, Oregon, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extra blank lines (Excel 2000)

    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. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> 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. <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

    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? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    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. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    Thanks for any ideas.

    Tia

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extra blank lines (Excel 2000)

    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?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Hillsboro, Oregon, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extra blank lines (Excel 2000)

    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. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extra blank lines (Excel 2000)

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

    <pre> Range("A1").Select
    Selection.CurrentRegion.Select
    ActiveWorkbook.Names.Add Name:="data", RefersToR1C1:=Selection
    Range("A1").Select
    </pre>

    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extra blank lines (Excel 2000)

    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

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Extra blank lines (Excel 2000)

    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

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extra blank lines (Excel 2000)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extra blank lines (Excel 2000)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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