How to delete empty rows?

Hi 4thSpace,

I suggest creating a filter that will only show your blank rows, then select and delete.

quinn

sort the table so the "blank" rows are all together then select the rows and select the menu item "Table > Delete Rows"

Hi 4thSpace,

If you do a sort ascending or descending I think your blank cell will end up grouped to gether at the bottom of your table. If you first create a column with numbers ascending (1,2 and drag down to fill) you can sort on that column to return to your original order after deleting blanks. Might be as easy to filter.

quinn

Post an example of what you have (omitting private or sensitive information). Otherwise it is hard to provide a specific example for a general question.

Hi 4thSpace,

I don't understand your difficulty with this. If you delete 3 from the sequence 1-5 they will still sort. These numbers need to be actual, not formula.

quinn

Quinn,

I have a similar challenge as the OP - I am looking to export / save a spreadsheet in csv format, but cannot have empty rows exported.

I can sort the data so all empty rows are together, and can manually delete the empty rows once they're together, however I haven't yet worked out how to use a keyboard shortcut or otherwise to delete the empty rows without manually selecting them.

Numbers of course doesn't permit a user to move to the last cell in a column or row that is not empty (which would allow me then to delete all rows below).

If I use an "ISBLANK" filter and then export to csv, the empty rows are exported also.

Is there a way of removing empty rows without haveing to manually select them?

I don't particulatly mind manually selecting them, however am looking to automate this as part of a longer process, and this is a bottleneck at present.

Thanks in advance for your time,

Try this:

Filter: show only rows that are 'not blank" (in a specific column)

Click on a cell to activate the table.

Click the tab for the topmost visible row.

Sift-click the tab for the bottommost visible row.

Copy.

Open a new Numbers document, using the Blank template.

Click once on the top left cell.

Edit > Paste Formula Results

Export the new document to CSV.

Regards,

Barry

Hi sterlingcurrency

The following AppleScript (written with Numbers 4.0.5) will delete all the empty rows in the frontmost table of the active sheet, providing at least one cell is selected in advance. It saves the original spreadsheet before proceeding, so you can revert to the last saved version if necessary. Nonetheless, I would strongly suggest creating a copy of your original data before trying it.

--begin script

tell application "Numbers"

try

display dialog "This script will delete all the empty rows in the selected table. Are you sure this is what you want to do?" buttons {"OK", "Cancel"} default button 2 with title "Delete empty rows..." giving up after 10

on error

return

end try

tell front document

save

tell active sheet

try --make sure we're targetting an active table

set active_table to first table whose selection range's class is range

on error number errnum

if errnum = -1719 then -- nothing is selected

display dialog "Select at least one cell in the table before running this script." buttons {"OK"} default button 1 with title "Delete empty rows..." giving up after 10

return

end if

end try

tell active_table

set {row_count, col_count} to {count rows, count columns}

--Using the number of columns in the table, set up a "dummy" blank row as a list of missing values:

set blank_row to {}

repeat with x from 1 to col_count

set blank_row to blank_row & missing value

end repeat

-- Step backwards through the rows, checking each against the dummy blank row and deleting any that match:

repeat with y from row_count to 1 by -1

set row_values to value of cells of row y

if row_values = blank_row then deleterowy

end repeat

end tell -- "active_table"

end tell -- active sheet

end tell -- front document

end tell -- Numbers

-- end script

It isn't particularly fast, as it deletes the empty rows one by one, and I'm sure it could be improved on - hope it helps though.

H

H,

That is not a script at all, but a thing of beauty!

It works just fine, fast enough and certainly effective.

Many thanks, I wish you well.

Out of curiosity - is it possible to achieve this result without using applescript?

Barry,

Thanks for taking the time to respond. I should have stated that I tried this, without success - the resulting csv file still contains the blank lines that were hidden by the filter.

The script below written by HD was an effective solution however.

😁

It's possible to automate Numbers using JavaScript and Objective-C. I don't know how to do either of those.

What the AppleScript does is basically what a human would do - check each row for blankness and delete it if it's blank. Its disadvantage is that it can't see whole blocks of empty rows and delete them in chunks, which we humans can. Its advantage is that it doesn't have to select anything before deleting it, which humans have to.

Understood, thanks again.

I am looking to export / save a spreadsheet in csv format, but cannot have empty rows exported

If you have "simple" data in your Numbers table (no embedded commas within cells) you can save data directly to a file, skipping blank rows in the table, with a short script like the one below.

1. Copy-paste script into Script Editor (in Applications > Utilities)
2. Click somewhere in the table
3. Click the triangle "run" button.

If you do have embedded commas you can use tab as delimiter.

set text item delimiters to tab

instead of:

set text item delimiters to ","

and change the extension of the default name to .tsv.

To simplify coding, this assumes any row that has no value in column A (first cell) is blank. It could easily check on another column instead by changing first to second, etc.)

SG

tell application "Numbers"

tell front document's active sheet

tell (first table whose selection range's class is range)

set vv to (rows whose first cell's value is not missing value)'s cells's value

end tell

end tell

end tell

set text item delimiters to ","

set dataStr to ""

repeat with r in vv

set dataStr to dataStr & r'sitems & return

end repeat

set f to choose file name default name "TestMyCSVData.csv"

open for accessf with write permission

writedataStrtof

close accessf