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 ;)