Selecting every 4th row

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

I several pages of lists where I need to pull every 4th row and send that
data to a new page. Is there a way to do this? Also, would like when it
does pull every 4th row and puts it in a new column to not have 3 blank
cells. Using Excel 2003.
 
A looping macro something like this
for i = 1 to ?? step 4
dr=1
rows(i).copy sheets("ds").cells(dr,1)
dr=dr+1
next i
 
In a helper column, create this formula:
=IF(MOD(ROW(),4)=0,"X","")
Autofilter on this column for "X", then select your data. It should only
copy the visible cells. If not, try select the column and then hitting Alt+;
to select only the visible cells. Copy to desired destination.
 
Use the following formula to extract every 4th row:

=OFFSET(C$3,((ROW()-ROW(H$2))*4),0,1,1)

Change the C$3 to the first cell of the list of data and change the
H$2 to the first cell in which you enter this formula. Include the $
characters as shown so Excel doesn't change them as you fill down a
range.

Then, copy this formula down as far as you need to go. It will return
every 4th value starting at C3, with no blanks between the values.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Back
Top