Log in

View Full Version : Excel question


RickJ
Jul 25, 2006, 11:58 AM
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
Jul 25, 2006, 12:19 PM
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
Jul 25, 2006, 12:22 PM
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
Jul 25, 2006, 12:45 PM
Sorry NeedKarma, we must have been posting at the same time. Had I seen your post I would not have posted myself.

RickJ
Jul 25, 2006, 12:53 PM
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
Jul 25, 2006, 01:27 PM
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
Jul 25, 2006, 01:47 PM
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
Jul 25, 2006, 01:51 PM
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
Jul 25, 2006, 02:30 PM
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
Jul 26, 2006, 03:17 AM
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
Jul 26, 2006, 04:24 AM
Rickj a good book to read is Excel for Dummies full of lots of info and tips ;)