Repetative Row Deletes

S

scratching my head

I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie: Row 1
is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to delete
Rows 2-5, 7-10, 12-15, etc. I would prefer for the 1st row (ie: Row 2) be
selected and then I can set a variable in the macro to tell it how many sets
of 4 consecutive rows (with one good row between each bad set of 4) I need
deleted.

Help is appreciated as I am not a programmer but guys are really good.

Thanks
Danny
 
N

Nick Hodge

Danny

Not sure of the last bit or whether you want a programming solution, but if
you just want to delete the four rows and keep the fifth, use a 'helper'
column.

Let's use column S as a helper column

1) Enter 1 in S2
2) Enter 2 in S3, 3 in S4, 4 in S5 and 5 in S6.
3) Highlight S2:S6
4) Drag this data using the fill handle (Small square bottom right S6) the
full extent of your data.
5) Highlight row 1 and apply an autofilter (Data>Filter>Autofilter...)
6) In the autofilter drop-down for column S select Custom
7) In the first drop-down select 'less than or equal to' and enter 4 in the
box along side
8) click ok
9) Select the data and press the delete key
10) Remove the filter (Data>Filter>Auto Filter...)
11) Highlight ALL the data (even the blank rows)
12) Sort it (Data>sort)
13) The deleted rows will now be at the bottom

That's a non-programming solution. If you need a programming one, post back
in the programming newsgroup

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top