I've got a big (58,000 rows) spreadsheet that I used as
the data source for a big Word mail merge a while back.
It contains the address of every household in the
district. I now wish to use the same spreadsheet but for
a sample survey, and only need every 10th address. So
basically I need to keep every 10th row and delete the
other nine, and be left with a spreadsheet containing
only 10 per cent of the original addresses.

What is the best way to do this? Is there a simple macro
that would do it? I tried recording myself deleting rows
but the macro works on absolute values so it only
recorded me deleting Rows 2-10 and that's all it does
every time I play it, so that's no good.

Or, failing that, is there a function/formula I could
employ to get the same effect?

It did occur to me to structure the Word merge so that
only the 10th record in the sheet was merged, but I
really need the sheet just to have the addresses that
were used in the merge this time round, not all the other
addresses as well.

Thanks for any help anyone can give on this.

Steve Wylie

Use a help column, in that help column use this formula adjacent to the
first cell with an address

=MOD(ROW(1:1),10)<0

copy down 58000 rows, then apply the autofilter and from dropdown select
TRUE, then select all visible cells and do editdelete, entire row

--

Regards,

Peo Sjoblom


"Steve Wylie" wrote in message
...
I've got a big (58,000 rows) spreadsheet that I used as
the data source for a big Word mail merge a while back.
It contains the address of every household in the
district. I now wish to use the same spreadsheet but for
a sample survey, and only need every 10th address. So
basically I need to keep every 10th row and delete the
other nine, and be left with a spreadsheet containing
only 10 per cent of the original addresses.

What is the best way to do this? Is there a simple macro
that would do it? I tried recording myself deleting rows
but the macro works on absolute values so it only
recorded me deleting Rows 2-10 and that's all it does
every time I play it, so that's no good.

Or, failing that, is there a function/formula I could
employ to get the same effect?

It did occur to me to structure the Word merge so that
only the 10th record in the sheet was merged, but I
really need the sheet just to have the addresses that
were used in the merge this time round, not all the other
addresses as well.

Thanks for any help anyone can give on this.

Steve Wylie

I haven't seen my earlier post to this so I am sending another.

You could modify Peo's formula to:

=IF(MOD(ROW(10:10),10)<0,"","X")

Leave your Excel file as is. In the Word mail merge under section 3 you can set a Query_Option to only select data with an X in this field.

Query Options are very useful for merging a selected portion of your total list.

Good Luck,
Mark Graesser


----- Peo Sjoblom wrote: -----

Use a help column, in that help column use this formula adjacent to the
first cell with an address

=MOD(ROW(1:1),10)<0

copy down 58000 rows, then apply the autofilter and from dropdown select
TRUE, then select all visible cells and do editdelete, entire row

--

Regards,

Peo Sjoblom


"Steve Wylie" wrote in message
...
I've got a big (58,000 rows) spreadsheet that I used as
the data source for a big Word mail merge a while back.
It contains the address of every household in the
district. I now wish to use the same spreadsheet but for
a sample survey, and only need every 10th address. So
basically I need to keep every 10th row and delete the
other nine, and be left with a spreadsheet containing
only 10 per cent of the original addresses.
What is the best way to do this? Is there a simple macro
that would do it? I tried recording myself deleting rows
but the macro works on absolute values so it only
recorded me deleting Rows 2-10 and that's all it does
every time I play it, so that's no good.
Or, failing that, is there a function/formula I could
employ to get the same effect?
It did occur to me to structure the Word merge so that
only the 10th record in the sheet was merged, but I
really need the sheet just to have the addresses that
were used in the merge this time round, not all the other
addresses as well.
Thanks for any help anyone can give on this.
Steve Wylie