Google Sheets - select column minus header (or deselect cell)

1
votes

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.

2
google-apps-scriptgoogle-sheets
I don't understand the negative feedback on my question. I searched for an answer, and didn't find anything. I know it is probably an easy thing to programmers, but I'm not, I'm a graphic designer that got thrown into needing to help with some spreadsheet stuff. I figured something like this should be possible, but I don't know scripting, so I thought I'd come to a helpful community for advise. If that warrants a negative rating, please let me know why so I can avoid such mistakes. – CBJ
The negative feedback likely arises from two sources, which are interrelated. First, you asked this on Stack Overflow (a programming Q&A site) but specifically requested to avoid a script (the only thing that might possibly make this programming-related). Second, you essentially asked for someone to write an entire solution for you, which is generally frowned upon by Stack Overflow members. We are happy to help you with code, but we don't usually write complete solutions. In the future, you might consider asking questions about how to effectively use Google Apps on Web Applications. – Cody Gray♦
If saying what I "asked", please reference where, as I don't recall where I asked anyone to "write an entire solution" for me. I had a question which I thought may have a very easy solution, but which I was unable to find an answer to, and thought perhaps people with more experience in this area might know. And I didn't request to avoid scripts, I said it would be great if there was an easy method I didn't know of. Is there something wrong with wanting to know an easy way if possible? The answer I got involved scripts, which not only helped, but taught me some things as well. – CBJ

2 Answers

1
votes

The quick manual way to do this is select the column(s), then hold down CTRL and select the header(s) you want to remove.

0
votes

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();
}