vba - Excel macro to Word mail merge is trying to merge ALL rows in the column -

i created vba form , macro generate auto-numbered documents company. depending on cost-center selected choose word document complete merge. generates alpha-numeric sequence in column under header 'firstname' (firstname played nicely word 'source' field).

everything seemed working fine until went produce sequence had leading zero. every leading 0 after 'manual' (starting number) entry form got auto-removed. did reading , ended checking len() of string , re-inserted 0 if less number of digits input (starting) number had. hope you're still following me here. :)

here think happened... had tried other things in beginning, including having macro write out sequence single quote ('), kept removing leading zeros before point. highlighted entire column , formatted text (so, technically 64,000+ rows?) again, same issue. work-around in last paragraph solved issue, merge attempting merge rows in column.

the answer may simple, looking stop when sequences stop? have 1 routine generates numbers, , generates many want:

for = 1 strformnums     if len(strstartval) < 4         rngformnumbers(i).value = strcampus & "0" & strstartval 'appends single 0 show before starting numbers (making 0800 instead of 800). otherwise excel lose leading 0 after first number.     else         rngformnumbers(i).value = strcampus & strstartval     end if      strstartval = strstartval + 1 next 

i have tested this. doesn't generate more than, say, 200 or 500 lines. here snippet mail merge, i'm thinking grabbing lines reason (it didn't before):

strworkbookname = thisworkbook.path & "\" & thisworkbook.name  wdocsource.mailmerge.maindocumenttype = wdformletters  wdocsource.mailmerge.opendatasource _         name:=strworkbookname, _         addtorecentfiles:=false, _         revert:=false, _         format:=wdopenformatauto, _         connection:="data source=" & strworkbookname & ";mode=read", _         sqlstatement:="select * `numbers$`"  wdocsource.mailmerge     .destination = wdsendtonewdocument     .suppressblanklines = true     .datasource         .firstrecord = wddefaultfirstrecord         .lastrecord = wddefaultlastrecord     end     .execute pause:=false end 

if had venture guess sql statement 'seeing' more rows because whole column has been formatted? did manual merge , sure enough there thousands of blanks after 200 numbers ended. filtered them out manually in word, fix because program meant automate process.

if needs see more code happy provide it. in advance.

after experimenting, decided remove formatting column using clear > clear formats (excel 2010). mail merge terminating @ first blank line instead of running through entire column.

it seems highlighting column , formatting text (for instance) can muck mail merge. if i'm mistaken please feel free correct me, seems have solved issue.