How to delete empty rows?

And here's a simple script to delete rows in a table whose first cell is blank.

SG

tell application "Numbers"

tell front document's active sheet

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

repeat with r from row count to 2 by -1

tell row r to if its first cell's value is missing value then delete it

end repeat

end tell

end tell

end tell

And a simple one that checks if the entire row is blank before deleting it (borrowing HD's idea of constructing a list of missing value):

SG

set aBlank to missing value

set allBlanks to {}

tell application "Numbers"

tell front document's active sheet

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

repeat with c from 1 to column count

copy aBlank to allBlanks's end

end repeat

repeat with r from row count to 2 by -1

tell row r to if its cells's value is allBlanks then delete it

end repeat

end tell

end tell

end tell

Improved script for exporting data from non-blank rows directly to a csv file.

SG

set {aBlank, allBlanks, vv} to {missing value, {}, {}}

tell application "Numbers"

tell front document's active sheet

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

repeat column count times

copy aBlank to allBlanks's end

end repeat

repeat with r from 1 to row count

set rVals to row r's cells's value

if rVals is not allBlanks then copy rVals to vv's end

end repeat

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 text item delimiters to "missing value"

set strAsList to dataStr'stext items

set text item delimiters to ""

set dataStr to strAsList as string

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

open for accessf with write permission

writedataStrtof

close accessf

SG,

This version is effective also - it turns out that when pasting the formula results via "Paste Formula Results", it leaves a space within the cells that do not have valid data in them. I've adjusted the script accordingly:

tell row r to if its second cell's value is " " then delete it

and it does exactly the job required. Out of curiosity, am I pasting incorrectly?

Hi sterling,

I'm surprised it didn't succeed for you. I had no time to test it last night, but did so after reading your post. Here are my results:

The top table is my original. All cells in column B displaying 1.00 contain the entered value 1, formatted to show two places after the decimal. The coloured text is a result of a conditional formatting rule. CSV files are plain vanilla text, so neither of these format attributes will be included in the export.

Column G contains entered text. No formatting beyond the defaults has been used in this column.

Values in the last column are calculated by a formula. The empty rows in the top table were added using option-up arrow to insert a row above the row containing a selected cell. The CSV export will contain the last calculated values, but not the formula that calculated them.

Because Numbers automatically filled the formula in column K into each new row, I deleted the content of those cells for the test.

The lower table is a copy of the upper one, created only to permit me to show both states of the table in a single image.

In the lower table a filter has been applied to Show only rows where Column K is not blank. Note the row numbers on the tabs to the left of the table (and compare then with the tabs on the upper table).

With the filter applied, I clicked on cell A1 of the lower table to select that cell, then pressed command-A to expand the selecion to include All the (visible) cells, then pressed command-C to copy these cells.

Following the instructions in my earlier post, I used a Blank template to open a new Numbers document, clicked on cell A1 of the table included in that new document, and pressed command-V to Paste.

The result is shown on the left part of the image below.

Note: the default table on my Blank template are 4 columns by 10 rows, and include 1 header row and 1 header column. Numbers adds columns to accommodate the extras in the copied values, but doesn't remove the empty row at the bottom due to there being less than 10 rows of labels and data in the copied values.

I noticed the empty row only after exporting to CSV, and decided not to delete the row then re-export.

Again,there are two tables visible in this image, this time located on different documents. The one on the left is the "New" document into which I pasted the data copied from the original document.

Again, note the numbers on the row tabs to the left of the selected table. No rows have been hidden. The only adjustment made to the table was to make the empty columns narrower to better fit the image into this space.

The new document, with a single table and no rows or columns other than the ones visible in the image was ready to export.

I selected File > Export To> CSV...

In the dialogue that opened, I gave the file a name (Test-hidden rows), leaving the .csv extension provided by Numbers, and clicked the Save button.

I located the saved file, right clicked it to get the Open With option and chose Numbers 3.2.6. The result was the Numbers file shown on the right. (I've adjusted the width of the columns to allow showing both documents at once, but made no other changes to the imported document).

The CSV docuemnt was imported as plain text, and adopted the default formatting for the cells into which it was imported—bold in the header row, normal elsewhere, and dropping the fixed two places after the decimal applied to the cells from which it was copied.

Finally, here's the same csv file opened in TextEdit to show the actual contents. Note that the decimal point and two zeroes were exported, but were dropped by Numbers when the file was imported, as they would be if typed directly into a cell without formatting set to show number values with two digits after the decimal.

Regards

Barry

sterlingcurrency wrote:

SG,

I've adjusted the script accordingly:

tell row r to if its second cell's value is " " then delete it

... am I pasting incorrectly?

After your adjustment the script deletes rows whose cell in column B contains one space character.

SG

I believe the best way is to use White Out. its available in any office supply store.

Hi sterling,

Oops!

Noticed I neglected to include the screen shot of the csv opened in TextEdit.

Finally, here's the same csv file opened in TextEdit to show the actual contents. Note that the decimal point and two zeroes were exported, but were dropped by Numbers when the file was imported, as they would be if typed directly into a cell without formatting set to show number values with two digits after the decimal.

Regards

Barry

"I believe the best way is to use White Out. its available in any office supply store."

Yeah! That should work out fine...

...or not. 😟