Filtered Excel worksheet creates blank labels, for instance, in mail merge for every "missing row".I have tried Edit/GoTo/Special/Visible cells only - but this does not impact the way Word/Mail Merge reads missing rows. Moreover, when mail merge reaches the last row, it grabs the address of the last entry and repeats it for several pages of labels.
I have sorted, filtered, hidden all unnecessary columns and highlighted the data. If I leave it highlighte, Word mail merge blocks access to worksheet warning it is being used by someone else. I clear the highlighting, am able to proceede, but blank labels are still produced.
The only thing that works is if I open a new worksheet, select Tools>Options>No zeroes, return to the sorted, filtered worksheet and hide unecessary columns, highlight visible data begining at last non-blank row, click on new worksheet tab, paste special> links (if I don't link the data, Mail Merege names every heading the same - something like "autodatfield").
Once the data is in the new sheet, mail merge works perfectly.
Is there something I need to do in Excel before a mail merge that would prevent the problem caused by the missing rows (filtered out)?
If I have to go through this tedious procedure each time, is there a macro that could do all this? I tried using record a macro, but it is sheet specific in Excel and cannot record steps for opening a new worksheet. swithching to the new sheet to preparing it accept data without insertining zeroes in empty fields. Further a recorded macro record the steps to open Word>Mail Merge, etc..
I have Windows XP, and Excel and Word 2000.
Thank you,
Trudy
