[RickJ]

I've got a spreadsheet of 1688 rows.

I only want rows 1, 9, 17, 25, 33, etc. (deleting 7 lines between each one I want to keep).

I don't want to just delete the contents of the other lines, I want to delete the lines themselves, so that I end up with current rows 1, 9, 17 etc. being 1, 2, 3 etc.

Is this possible?

Thanks!

[NeedKarma]

Uh you, just click on the first row number and drag down to the last of the seven rows until those rows are highlighted then right-click and select Delete.

Edit: unless you were looking to automate the process then I'm not up to VB snuff. :(

[rudi_in]

Thank you for posting your question to the Ask Me Help Desk.

You should be able to do this task. Try this:

1. highlight the rows you want to delete
2. right click
3. choose delete

Your once 'row' 9 should become 'row 2'

etc.

[rudi_in]

Sorry NeedKarma, we must have been posting at the same time. Had I seen your post I would not have posted myself.

[RickJ]

Thanks Guys. Yes, I'm looking to automate, if possible.

1. highlight the rows you want to delete

That's exactly what I'm trying to avoid. I've got 1688 rows.

I realize I can also select everything then deselect rows 1, 9, 17 etc, which would be less work... but still a lot...

... so no way to tell Excel to "delete everything except every 8th row?"

[Curlyben]

OK think of this from a different angle.
What makes these lines different from the rest?
It maybe a simple case of using an auto filter to select the lines you want to delete and then just nuking them as mentioned previously ;)

Eg the lines you want have #1 in the A column and the others don't.
Select auto filter and then use the drop down to select the other lines deleting as you go ;)

[RickJ]

Thanks for that. I've never used the filter before. I'll play with it and see if I can get it to cooperate.

[Curlyben]

Failing that mail it to me and I'll do it for you.
I know how you "old" boys have difficulty with new functionality ;)

[StuMegu]

Record a macro to do the selected function and then just put it in a loooop!

Using relative references start the recorder using tools -> macro -> record new macro

When finished you can just repeat the macro or edit the code to make it loop a certain number of times automatically!

I did this for loads of spreadsheets in my previous job and it works a treat. If you use it a lot you can assign hotkeys to the macro or a button on a toolbar!

[RickJ]

Curly and Stu, thanks so much... but I played with each long enough to realize that in that time I could have been quite a way through the list selecting and deleting the rows... so I'll just gut it out and delete the unneeded rows manually. My guess is that it will only take about 3 beers :p

[Jay_Jay]

Rickj a good book to read is Excel for Dummies full of lots of info and tips ;)