# Is it possible to refer to a whole column in Google Spreadsheets using R1C1 format?

In Google Spreadsheets I often use A1 notation to refer to a whole column:

``````A1:A
``````

For reasons I explain below, I'd like to use R1C1 format, to refer to a whole column, something like:

``````R1C1:C1
``````

It's not possible to use A1:A in Excel, so as R1C1 notation originated from Excel, it seems unlikely that its possible to do this in R1C1 notation. If it is, it'd make my life easier.

Background: I have a spreadsheet visits, which pulls information from another spreadsheet data, which has a dynamically changing format.

To get around this, I use a formula to generate an address in a cell, based on the current layout of data. I can then reference this address cell in other formulae using indirect. And it's much easier go generate an address using R1C1 than A1.

-
"As it's not possible to use A1:A in excel" - do you mean "As it's not possible to use R1C1 in excel" ? – Vidar S. Ramdal Oct 18 '12 at 12:22
No, I meant that the range A1:A only works in Google Docs - with excel you have to specify both row and column for the end of a range. – s6mike Oct 18 '12 at 18:21
I can't read R1C1 notation without wondering about a R2D2 one. – Sony Santos Jul 13 '15 at 16:39

No, but you can work around it, by knowing which row is the last row with data in a column, using the `COUNTA` function.

`INDIRECT("A1:A")` could be written something like `INDIRECT("R1C1:R" & COUNTA(R1C1:R1000C1) & "C1")`

You need to determine a sensible 'max rows' value. I've used 1000 in the example, so this formula should work in a spreadsheet with up to 1000 rows.

Look up `COUNTA` in the Google Spreadsheets function list.

-

A1 notation is supported now. To center column A:

``````SpreadsheetApp.getActiveSheet().getRange("A1:A").setHorizontalAlignment("center");
``````
-
A1 notation has always been supported, but my questions asks whether it's possible to to use R1C1 notation to refer to a whole column in Google Spreadsheets. – s6mike Nov 25 '15 at 22:56