google-sheets

Select column minus header (or deselect cell)


I've been looking around for a solution, and the only thing I've found that doesn't involve scripting requires manually going to the bottom of each column. My spreadsheet does contain blank cells, so I can't use ctrl shift down. I am wanting to be able to select a column (or group of columns), but exclude the headers. Or, an alternative would be select all, and manually deselect the header row. According to Google, deselect is currently not available on Sheets. It would be great if there was some easy method that didn't involve scripts, as I really don't know scripting much at all, and this is something that I'd be using multiple times on lots of sheets, so having to copy scripts across sheets constantly doesn't sound appealing. I know in theory if a successful script is made that an add-on can be created, but that's also something I am not familiar with.


Solution

  • Select Columns but Skip Header Rows

    It may not be perfect but here's something that may help. You can set n to anything you want to. It defaults to 2 so the first row is left unselected. But you could create other versions as shown below:
    function selectColumnMinus(n) 
    {
        var n=(typeof(n)!='undefined')?n:2;
        var ss=SpreadsheetApp.getActiveSpreadsheet();
        var sht=ss.getActiveSheet();
        var cols=sht.getActiveCell().getA1Notation().replace(/\d+/,'');
        var rngs=cols + n + ':' + cols;
        var outrng=sht.getRange(rngs);
        outrng.activate();
    }
    
    function minus3()
    {
      selectColumnMinus(3);
    }
    

    Just make sure that the upper left corner of your selection is in the column you want.

    I got to thinking that may selecting more than one column might be useful too. So here it is. It also provides a prompt for entering the number of rows to skip at the top.

    function selectColumnsSkipHeader() 
    {
        var resp=SpreadsheetApp.getUi().prompt('Rows to Skip', 'Enter numbers of rows to skip at top.', SpreadsheetApp.getUi().ButtonSet.OK);
        var skip=Number(resp.getResponseText());
        var skip=(typeof(skip)!='undefined')?skip:1;
        var ss=SpreadsheetApp.getActiveSpreadsheet();
        var sht=ss.getActiveSheet();
        var rng=sht.getActiveRange();
        var rngA= sht.getActiveRange().getA1Notation().split(':');
        var ul=rngA[0].replace(/\d+/,'');
        if(rng.getNumColumns()>1)
        {
          var lr=rngA[1].replace(/\d+/,'');
        }
        else
        {
          lr=ul;
        }
        var rngs=ul + ++skip + ':' + lr;
        var outrng=sht.getRange(rngs);
        outrng.activate();
    }
    

    I played around with this a little and another way avoids the need of a prompt to get the number of rows to skip. Instead just position the top of your selection on the row that you want the selection to start on.

    function selectColumnsSkipHeader1() 
    {
        var ss=SpreadsheetApp.getActiveSpreadsheet();
        var sht=ss.getActiveSheet();
        var rng=sht.getActiveRange();
        var rngA= sht.getActiveRange().getA1Notation().split(':');
        var ul=rngA[0].replace(/\d+/,'');
        if(rng.getNumColumns()>1)
        {
          var lr=rngA[1].replace(/\d+/,'');
        }
        else
        {
          lr=ul;
        }
        var rngs=ul + rng.getRow() + ':' + lr;
        var outrng=sht.getRange(rngs);
        outrng.activate();
    }
    

    Demo for recent question:

    I have this command as a utility in a drop down menu and I'm just showing the questioner how it works.

    enter image description here