Excel for People who don't know anything

December 2, 2011 9:05 AM

Excel issue. Tried looking on the internet, had no success. Can y'all help? Selecting and deleting multiple nonadjacent rows.

I need to remove every tenth row from some spreadsheets which have between 500 and 5000 rows. Each row has 10 columns. Each column has text in it. I have been doing this for a couple days and at first it was working. I was clicking the first row to select the row, then holding down ctrl, clicking the next row (10 rows down from the first row selected), and so on, then hitting ctrl-x and removing the rows. Worked fine--if an extremely tedious task.

It is no longer working. When I click the first row, it selects the row. When I hit ctrl and click the next row, it selects the next row as well but there is now a box around the first cell in the next row and when I hit ctrl-x, I get an error message: "The command you chose cannot be performed with multiple sections. Select a single range and chose the command again."

I am clicking the row number to select the row and as far as I can tell, nothing is selected or highlight prior to my clicking the first row. I am able to select a row, and hold down the shift key to select multiple adjacent rows for cutting, but I cannot select multiple nonadjacent rows. I was able to do it before and I see the behavior across more than one spreadsheet in the same directory.

It's driving me nuts! any suggestions (other than getting the proper software tool or software to do the job, which is never in a million years going to happen).

posted by crush-onastick to technology (18 answers total)

Oh that's weird! I was able to duplicate your problem and I think I MIGHT know what the bug is. Is your text formatted as a table? In other words, between the time it worked and now that it doesn't work, did you select your text and under Table Tools/Design, select the 'Format as Table' option to make it all pretty? I ask because it seems like I can still control select and delete empty rows that I do not have formatted as a table, but if I try to choose rows within my table, I have the same problem.

However, I would be using the right-click/delete option to remove your rows, not ctrl-X (because that's just moving your rows to memory and could slow things down a bit).
posted by Eicats at 9:14 AM on December 2, 2011

p.s. sorry that's not really giving you a solution, but it might identify the problem. Not sure if it could be fixed by stripping the formatting because I don't want to try that on mine. ;) I know there are visual basic macros that would work perfectly for this though, since it's always the 10th row you want to delete...
posted by Eicats at 9:16 AM on December 2, 2011

Would this work for you?

1) Insert two new columns at start/end of data
2) In the first, type 1 in the first row, 2 in the second, then fill down to the bottom.
3) Type 1 in the first row, 11 in the eleventh, select, and then drag down, putting a 1 in every 10th row, leaving the rest blank.
4) Sort you data by the second new column, delete all the rows with a 1 in the column in one go
5) re-sort by your first new column to get the original order back, delete the new columns to get your data back where it started, but with every tenth row missing.
posted by chrispy108 at 9:16 AM on December 2, 2011

Oopps, 3) 1 in the eleventh, not 11.

Hopefully that made sense though
posted by chrispy108 at 9:17 AM on December 2, 2011

This has happened to me as well, in excel2003... when you click on the rows to highlight them, make sure you click in the middle of the row number, and don't click on any cells or rows at all, and you'll be able to delete multiple non-adjacent rows, and make sure you don't click on one row twice.
posted by Grither at 9:19 AM on December 2, 2011

Don't Ctrl-X. right-click and "Delete".
posted by misterbrandt at 9:20 AM on December 2, 2011 [1 favorite]

Grither: "on any other cells or rows at all,"

FTFM
posted by Grither at 9:20 AM on December 2, 2011

Also, what misterbrandt said!
posted by Grither at 9:21 AM on December 2, 2011

Seconding misterbrandt.

Also, as a "pro tip" the MOD function, which says whether something is divisible by another number, might be helpful.

You'd make one column (let's call it 'A') that just goes down the spreadsheet 1,2,3,4,5,..., and a second column with the formula "=MOD(A1,10) = 10". This formula will say "TRUE" for every tenth row, and FALSE for all the others. Then you can use the Filter tool to display only the TRUE rows and delete them in one fell swoop.
posted by losvedir at 9:24 AM on December 2, 2011 [2 favorites]

If it is every tenth row, then create a non-alphabetical/non-numerical sequence in a column, drag it down every 10th character should be the same.

Filter for that.

Select and delete those rows.
posted by MuffinMan at 9:25 AM on December 2, 2011

eicats: i don't recall formatting the files, but i am not the only person with access to them. I don't see a way to strip formatting. do you know where it is?

misterbrandt: when i right click, i can only select one row at a time and I'm trying to select multiple non adjacent rows, which i was able to do last week, but not any more.

lovesdir: that is great information, and may just be the best solution, even if it doesn't explain why the sheets are now behaving differently than before.

I'm trying to avoid the solution suggested by chrispy108, although it's helpful, as that requires more typing before deleting.
posted by crush-onastick at 9:47 AM on December 2, 2011

If you follow either my or losvedir's rules, you get to a filtered selection with just the rows you want to delete. At that point you can just select the whole lot without using the ctrl button and delete either with a right click or through the toolbar.

Apart from anything, it's a damn site quicker than what you are doing currently.
posted by MuffinMan at 9:50 AM on December 2, 2011

No, I mean: select the non-contiguous rows as you have been - by clicking and ctrl-clicking on the row number. Then, to delete them, right-click on one of the selected rows, and "Delete". Does that not work for you? Works here for me.
posted by misterbrandt at 9:52 AM on December 2, 2011

ah! that does work, misterbrandt, even though I still get the weird extra box! thanks!

yup, muffinman, the solution of adding a first column and sorting by it is faster.

multiple problems, solved!
posted by crush-onastick at 10:01 AM on December 2, 2011

I agree that you should "Delete" instead of "Cut," and since you're already used to ctrl-X you may as well use ctrl-- [control-dash] as the Delete keystroke. Saves the trouble of accidentally right clicking a separate cell and losing all your selected rows.

Are you using Windows or Mac? I ask because I use the Mac version of Excel, and can replicate your issue if I use "Cmd-X" for Cut. However, in order to Delete the rows, I actually have to use "Control-X" and it works (or to remove rows, "Control--").

I don't know if this works in all versions, but another way of selecting rows is in the upper left area, typically on the same level as the formula input field. This is where you can enter a range of rows/columns. I haven't checked if you can select the entire row this way, but since you know that you only have 10 columns and you only want every 10th row, you might be able to type in something similar to the following:

a10:j10, a20:j20, a30:j30

which will select those ranges. Hit ctrl-- and a pop-up menu will ask if you want to shift the cells up; select "Entire Row" and hit "OK"
posted by CancerMan at 10:11 AM on December 2, 2011

It's not really a bug: excel 2003 doesn't know how to cut multiple selections that aren't touching each other. Delete works just fine though.
posted by bonehead at 10:32 AM on December 2, 2011

bonehead: except I couldn't get the delete option either in rows that had been formatted as a table. Having worked for Microsoft in the past, we were instructed to call that "an unintended feature"; no longer working for Microsoft, I now call it "a bug".
posted by Eicats at 1:46 PM on December 2, 2011

we were instructed to call that "an unintended feature"

Probably by the same people who fill all those KB articles with "this behavior is by design" :-)
posted by flabdablet at 7:59 AM on December 3, 2011