How do I remove blank rows from my Numbers Spreadsheet?

Welcome to Apple Discussions

1. No, find & replace is for content. What would you replace it with?

2. Yes, select the rows & then Table > Delete Rows or right-/control-click & choose Delete Rows. Because it is available as a menu item, you can use System Preferences > Keyboard > Keyboard Shortcuts > Application Shortcuts to create a key combination to do it quickly.

Sort the table by any column containing data (preferably one with data in all rows except those rows that are completely empty). This will bring all the blank rows together either at the top or the bottom of the table. Select them all and delete.

Regards,
Barry

marycoleman721 wrote:
Thanks for the help. I wonder why my Numbers worksheet is running so slowly...

Hi Mary,

For clarity in communication it's always advisable to use the vocabulary specific to the application. It took me a couple of times through this post before I decided you were referring to Tables, not Sheets when you said Worksheets.

How big are the tables in your document? How many header rows and header columns does the table have? How many are there? What formulas are you using? All of these can be factors in the speed with which Numbers runs.

Numbers often balks at large tables, and the fact that it recalculates immediately when something changes can bring about perceptible delays.

A search of the forum for 'slow' or 'slowness' willl bring up some threads on this topic.

Regards,
Barry

Find / replace doesn't apply to rows but to cell's contents so it can't find the blank rows.

The sort tip described by Barry is efficient but it has a drawback :
the order of filled rows will be modified.

Here is a slightly enhanced one.
It use a supplementary column located at the right edge of the table.
its first standard cell contains the formula :
=IF(COUNTA(OFFSET($A$1,ROW()-1,0,1,COLUMN()-1))>0,ROW(),"")
Fill down
With that, we may sort upon this column.
The blank rows will be gathered at bottom but the ordering of other ones will not be changed.

Yvan KOENIG (VALLAURIS, France) 1 septembre 2010 09:10:42

Another method, assuming there is a column you can use to determine blank versus non-blank, is to categorize by that column. This will put all the blank rows together and you can delete them all at once. Then un-categorize your table. It might rearrange the sorting of your table, though.

Yvan,

This somewhat simpler expression seems to do the same thing.

=IF(LEN(A)>0, ROW(), "")

Am I missing anything?

Jerry

Jerrold Green1 wrote:
Yvan,

This somewhat simpler expression seems to do the same thing.

=IF(LEN(A)>0, ROW(), "")

Am I missing anything?

I choose to check that every cells of a row (before the one in which is the test formula) are empty.
You test only cell of column A.
I think that my formula is more complete but of course it depends of which kind of datas are stored in the table.
I have a lot ot table with nothing in cells of column A but values in other columns.

I wish to add a boring info.

My Powermac G5 bought at end of 2005 died : motherboard dead.
My datas from the forum are on its internal HDs (they don't match the definition “important datas”) so I will unable to reach them until next week when I will receive enclosures in which I will put the devices.
So, a lot of my custom tools will not be available making me less efficient.
It's really pitiful : I never got an Apple product with so short life.

Happily its older 'brother', a Powermac G4 is always alive so I will be able to run some oldies under Classic.

Yvan KOENIG (VALLAURIS, France, 2010/09/02 21:11:36)

KOENIG Yvan wrote:
I choose to check that every cells of a row (before the one in which is the test formula) are empty.
You test only cell of column A.
I think that my formula is more complete but of course it depends of which kind of datas are stored in the table.
I have a lot ot table with nothing in cells of column A but values in other columns.

Ah! So this expression in Row 2 of the Aux. column and fill down should work:

=IF(COUNTA(2:2)>1, ROW(), "")

No?

I would like to pay my respects to the fallen Mac. May his older brother continue to live on and prosper.

Jerry

Jerrold Green1 wrote:

=IF(COUNTA(2:2)>1, ROW(), "")
No?

One more time, yes and no.

Yes if you take "empty" cell at its true meaning.
No, if you take that with the often used meaning : blank or contain a string whose length is zero.
For such a test, I felt that the second acception (which I don't like) may be valid.
So I built my formula upon it.
It checks that the result of the concatenation of every cells on the left of the formula is a value whose length is zero.

I repeat that it's a matter of choice.
Only the user know exactly which is the one matching its needs.

I know that mine is not perfect, my preferred one would be with the formula in column A so that adding new column wouldn't change its behaviour but I quickly thought that it wouldn't be clean in the tables.
As aspect is an important feature of Numbers, I choose to put the formula in the "Far East".

Most of the time, I don't explain why I choose this or that soluce. This time you entered in my "kitchen" 😉

Yvan KOENIG ( VALLAURIS, France ) jeudi 2 septembre 2010 22:34:58

I would like to pay my respects to the fallen Mac. May his older brother continue to live on and prosper.

Oops,
I forgot to say thank you for your condolences.
It's a pity to be dropped by what I feel to be the best design ever issued by Apple.

Question : Am'I the only one receiving spams for viagra or similar products in a mac.com mailbox ?

Message was edited by: KOENIG Yvan

I enter your kitchen one more time, with:

=COUNTBLANK(2:2)<COLUMN()-1 where the aux column is far East.

Jerry

Quite OK.

I forgot that COUNTBLANK count a cell containing a nil string as if it is blank.

=COUNTBLANK(2:2)=COLUMNS(1:1)-1

which will correctly return TRUE even if we insert columns after the test one.
Alas, it will return FALSE in others and it would be ugly.

I prefer :

=IF(COUNTBLANK(2:2)=COLUMNS($1:$1)-1," *";"")

I used asterisks so that the marker isn4t linked to a given language.

Yvan KOENIG ( VALLAURIS, France ) jeudi 2 septembre 2010 23:47:43

marycoleman721 wrote:
Well, I am very grateful for the elaborate fixes, but I must admit the last few were quite over my level of expertise. As Barry so kindly pointed out, I can't even speak the "language of the application".

I'm guessing the easiest way for me to eliminate the blank rows is sort by a column which contains data in all the rows, then delete blank rows, then uncategorize. I thought uncategorize would return the blank rows to their original position.

"Sort" rearranges the rows in order using the contents of one or more columns as the key for the sort. You do have the opportunity to Undo Sort in the Edit menu, but that's not terribly useful in this case as Undo is done step by step in reverse order; as you would have deleted the blank rows after doing the sort, you would have to Undo that deletion before Undo Sort became available.

Insert Categories (in the Reorganize Dialogue) also involves a Sort (on the same column as used to insert the Categories), and unclicking the InsertCategories checkbox, while it does remove the Category rows, does NOT return the undeleted rows to their original order. 😟

Yvan's suggestion and formula takes care of that problem by using a formula places a number in the cells of the rightmost column of all rows which contain any data in the columns before that cell.

It use a supplementary column located at the right edge of the table.

its first standard cell contains the formula :

=IF(COUNTA(OFFSET($A$1,ROW()-1,0,1,COLUMN()-1))>0,ROW(),"")
Fill down
With that, we may sort upon this column.
The blank rows will be gathered at bottom but the ordering of other ones will not be changed.

COUNTA(OFFSET($A$1,ROW()-1,0,1,COLUMN()-1))>0
This part of the formula counts the number of cells in the row containing data, and compares the count with zero.
-If the count is greater than zero, the next part of the formula is used.
ROW()
This part places the row number into the cell containing the formula. Because row numbers increase as we move down the spreadsheet, these row numbers are already in ascending order, and an ascending sort on that column will not change the order of these rows. After the Sort, the formulas will be recalculated, and the numbers (but not their order) will change.
-If the count is zero (ie. All cells in the row to the left of the formula contain no date, then the last part of the formula is used.
""
This part places the empty string ( "" ) into the cell containing the formula.
Text values (including the empty string) sort after numerical values, so an ascending sort on the column will place these rows together at the bottom of the table.



As written, Yvan's formula assumes that ALL cells in the 'empty' row, including cells in header columns, are empty. If that's not the case, and you want to consider only the 'standard' cells in determining whether a row is empty, you'll need to make two changes to the OFFSET part of the formula.
OFFSET($A$1,ROW()-1,0_*,1,COLUMN()-*_1)
The underlined zero is the column offset (from cell A1) where the count starts. Change this number to the number of header columns in your table.
The underlined one is an adjustment to the number of columns in the range of cells counted. Change that number to one more than the number of header columns in your table.

The rest of the discussion between Jerry and Yvan is regarding possible substitute formulas for the one above. While there may be advantages to one or more of these, you can safely ignore the discussion and use Yvan's first suggestion (with the modifications suggested here if there's a need to eliminate header column cells from the count). After all, you won't be stripping blank rrows from your table every day.

On that note, I'd suggest that after deleting the empty rows you take one further step:
Either delete the new column that was added to contain the formula,
OR delete the formula from all rows except the first standard cell in the column.
Either step will remove the formula from Numbers's calculation load each time and value on the table is changed. The second will keep a single copy of the formula which you can fill down the column should you need to repeat the exercise in the future.

Barry, I have 5 header columns and 5 header rows, and the only formula I use is "Sum", however I use this formula on 70 columns and 3,921 rows. Of the 3,921 rows, approximately 1/3 of them are blank, and I'd love to remove them to shorten my scrolling. But you are right, Numbers is being very sluggish. I exported this table to Excel to see if it was also sluggish, and it is not. I like working with Numbers better. All my other work is in Numbers, and I'd like to keep things together.

As mentioned earlier, Numbers is sluggish with large tables, and for Numbers, a 4000 row table (with over a quarter million calculations) qualifies as "large". Deleting the blank rows should speed things up somewhat.

The reason I asked about header rows and header columns was that I had noted a recent post stating that multiple header columns also slowed Numbers. The link will take you to that thread (and message).

Regards,
Barry