Deleting blank lines within a MS Excel spreadsheet

Questions & Suggestions
#1

Hi Guys, new to Keyboard Maestro and have successfully setup quite a few macros using it. Its my first post here so not entirely sure if I am posting in the correct area and providing the required level of information. If not I apologise and if you let me know what is required I will provide.

There is one thing I would like to do that is causing me some head scratching.

I have an excel spreadsheet that has some cells in it where there are lots of blank lines causing the overall cell size to be far too large.

I want to remove these blank lines but am not sure where to start. Can anyone please give me some pointers (have tried the copy to clipboard and remove whitespace and pasting back but this doesn't seem to work).

#2

Hello @hueylew -

I sometimes see similar here with NeoOffice spreadsheet, not Excel.
But perhaps the causes are the same, so we look for how to solve this problem.
(Maybe use Keyboard Maestro; maybe not.)

First, have you tried Find-Replace for multiple carriage returns?

:leftwards_arrow_with_hook:︎
:leftwards_arrow_with_hook:︎
:leftwards_arrow_with_hook:︎

If not, try that first.
If problem continues, then we have other options

Any questions, you are welcome to ask.

#3

Hey Adam,

Please create a sample Excel worksheet with as true-to-life an example as you can.

If you’re dealing with more than one cell at a time the worksheet should reflect that.

Zip the worksheet, and drag the zip file to the Keyboard Maestro Forum editor window to upload it.

It should be easily possible to do what you want with a combination of AppleScript and Keyboard Maestro actions.

-Chris

#4

Hi Guys, sorry for the delay in providing an update on this. I have a sort of method of achieving what I was after, as suggested by Chris, a combination of KM and AppleScript. Not the most elegant but does the job for me. Any suggestions for how to improve would be gratefully received.

Basically, it takes the currently selected cell, copies the text and pastes it into TextEdit. Runs an AppleScript to remove blank lines and then copies it back into Excel. For some reason though I couldn't get it to just paste back into the same cell which is why I have the mouse action present to simulate double clicking into the cell I want it to go in.

Remove Whitespace.kmmacros (6.2 KB)

#5

You don’t need to use TextEdit.

EDIT: Added Loop to remove all blank lines in each Cell in the Excel Selection
This script will remove all blank lines in the Excel Active Cell

### HOW TO USE
#  Select the cells in Excel
#  Run this script

tell application "Microsoft Excel"
  
  
  repeat with iCell from 1 to count large of selection
    set oCell to cell iCell of selection
    set value of oCell to my removeEmptyLines(oCell)
  end repeat
  
  
end tell

on removeEmptyLines(poCell)
  
  tell application "Microsoft Excel"
    
    set cellValue to value of poCell
    set paraList to paragraphs of cellValue
    
    set cleanList to {}
    
    repeat with oItem in paraList
      if oItem as text ≠ "" then set end of cleanList to oItem as text
    end repeat
    
    set AppleScript's text item delimiters to return
    
    set cellClean to cleanList as text
    
    return cellClean
    
  end tell
  
end removeEmptyLines
1 Like
#6

and for a variant using an ‘Execute JavaScript for Automation’ action, you could try something like:

(function () {
    'use strict';

    Application("Microsoft Excel")
        .selection.cells()
        .forEach(function (c) {
            c.value = c.value()
                .replace(/[\r]{2,}/g, '\r');
        });
})();
1 Like