Filtered Excel data produces blank labels in Word>MailMer

lburg801

New Member
Joined
Oct 25, 2005
Messages
14
:rolleyes: 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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
I'm no expert on this as I haven't used it for a long time - but I seem to remember that there is a filter option as part of the mail merge process. I would suggest that you filter the data at that point rather than pre filter it in excel.

Hope that helps you


P.S it only appear in red to you to signify that you have posted on the thread
Any post that you have contributed to will appear as red to you.
 

lburg801

New Member
Joined
Oct 25, 2005
Messages
14
I'll try again to see if there is a way to filter in Mail Merge, but it wasn't obvious to me when in the midst of doing the various merges.

And thanks for explaining the red. I found it rather embarassing as it seemed bad "netiquette". and I had no idea how I had caused it.

Trudy
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,071
Latest member
hijackhippo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top