selecting every 5th row?

museumgirl

New Member
Joined
Oct 8, 2002
Messages
1
Hi all-
I have a mailing list of about 1600 names on Excel (from a raiser's edge database). I want to do a samping of these addresses to send out a survey. Is there a way (through a macro?) to pull every 5th record out to produce mailing labels?

thanks you for your help,
abigail
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Maybe try this. Add a temporary column to your table. Format the cells as TEXT. In the first row, enter 1, in the second, enter 2. Use filldown to populat the column. Now do an Auto Filter selecting Custom. CHosse Ends With 0 OR Ends With 5. Copy the filtered list ot a new worksheet.
 
Upvote 0
Sure there is. I don't do much with Excel's features, I rely upon VBA, which may not be the way to go.

I could imagine a macro "working backwards" from the end of the list. You could position it at the end, it could find it's way to the end.

At that point, the .Row property would be given to the macro, it would Step -5 towards the beginning of the list, all the while transferring every 5th line to a new sheet.

I would hold off on implementing this technique, until the wizards confirm/deny this as the best way to go.

Steve
 
Upvote 0
On 2002-10-09 15:58, museumgirl wrote:
Hi all-
I have a mailing list of about 1600 names on Excel (from a raiser's edge database). I want to do a samping of these addresses to send out a survey. Is there a way (through a macro?) to pull every 5th record out to produce mailing labels?

thanks you for your help,
abigail

Hi abigail:

Let us say your address list is in cells B1:B20, then you can do this in the worksheet by writing the following formula in cell C1 and copying it down

=INDEX($B$1:$B$20,(ROW(1:1)*5-5))

See the following worksheet simulation ...
y021009h1.xls
ABCD
11JohnDoeandCoJohnDoeandCo
22KaySmithCoMrExcel
33JaneDoe.PieterBoekhout
44BobBryerLaraAnand
55MrExcelmuseumgirl
66DaveSpiers
77PamMcNeil
88RegBeasley
99TomHanks
1010PieterBoekhout
1111FrankYono
1212Joe
1313Rick
1414KaySmithCo
1515LaraAnand
1616MVP
1717BoardMaster
1818WallyStreet
1919JaneVit
2020museumgirl
Sheet10
</SPAN>

Regards!

Yogi
 
Upvote 0

Forum statistics

Threads
1,214,683
Messages
6,120,871
Members
448,992
Latest member
MerryTaylor

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
Back
Top