Easiest Way to Delete Rows

B

Bill

For Excel 2007

I would like to a repetitive pattern of rows delete rows from a very large
worksheet with 2000 rows. As example, I would like to delete rows 1, 2, 4, 5,
7, 8, and so on. I would like to keep rows 3, 6, 9, and so on. In this
example, I am deleting two consecutive rows, keeping the next row, deleting
the next two rows, keeping the next row, and so on. There is a pattern of
deleting rows.

I would like to delete these rows in the easiest manner possible, without
deleting each row individually. Also, I would not want to write a macro if
possible.

Any suggestions are appreciated.

Thank you
 
F

Fred Smith

I would use the Data Filter. If your data is such that you can filter out
the rows you want to keep, then do so, and delete the visible rows.

If not, insert a column with the formula:
=if(mod(row(),3)=0,"Keep","Delete")

Then use Data Filter to select the "Delete" records, and delete them.

Regards,
Fred
 
J

Jim Birke

I've never had a worksheet with 2000 rows in it but, here's what I do:
If you want to delete a series (i.e. 1,2,3,4) of rows, highlight the first
one in the series then hold down the shift key and highlight the last one.
(The series will now be highlighted.).
Click on the home tab and click on the word 'delete' in the 'cells' tab. A
short menu will pop up. Click on 'delete sheet rows' and the rows go away.

If you want to delete rows that are not in series (i.e. 2,5,7,9), highlight
the first one then hold down the Ctrl key and highlight the others one at a
time. Follow the above instructions to delete them.
This might be painfully elementary but it's the best I can suggest.
 
E

excelent

If u got ur data in column A and B is free :

1. In namebox type B1:B2000 and hit Enter
2. type =mod(row(),3) hold CTRL and hit Enter
3. Rightclick in selection and select Copy
4. Rightclick and select insert special > Values
5. Hit Ok and Esc
6. In namebox type A1:B2000 and hit Enter
7. Sort column B acending
8. Select all cells in B > 0
9. Delete selected rows


"Bill" skrev:
 
D

Dana DeLouis

worksheet with 2000 rows.
I would like to keep rows 3, 6, 9, and so on.
Also, I would not want to write a macro if possible.

One of many ways, although a custom macro is the easiest.

Assume Data in Columns A:D, and the first empty Column is E:E
Place an 'x in E3.

Type E1:E2000 in the name box to select the range.
Select Home | Editing | Fill | Series ...

Select Type "Autofill"

Select F5 | Special | Blanks

Right Click a cell | Delete... Entire Row

Clear Column E:E

= = = = = = = = =
HTH :>)
Dana DeLouis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top