-2

I want to have a code that when it runs, it fills the entire column A of the active sheet with a continuous series of numbers starting from 1 like 1,2,3,... . The numbers should start from cell A2. The result should be as follows:

A2 = 1 ; A3 = 2 ; A4 = 3 ; etc. until it reaches the last row of the sheet.

I have no idea how to write the code!

Any help?

Omid
  • 177
  • 2
  • 2
  • 10
  • 1
    Sorry, but this shows absolutely no attempt at all. Given your stated requirements, you can do this with the most basic of spreadsheet functions. – Diego Jul 16 '18 at 15:56
  • @Diego Actually I have written a lot of code so far by doing a lot of blind searches. I am a fan of coding and love playing with codes. But the problem is I am a true novice and can only manipulate existing codes. I cannot start coding from scratch. The reason I didn't include any code is that I couldn't find any hints regarding where to start. You can at least give a hint as to which commands I should use. I will start my search from there. – Omid Jul 16 '18 at 16:24
  • 1
    It's not clear what is this question about. We could assume that it refers to Google Sheets but not if this is about Google Sheets macros or Apps Script by the other hand the question doesn't include any description of the efforts made. – Rubén Jul 16 '18 at 17:15
  • If you are required to achieve it using GAS, I think that you can achieve it by putting ``=ROW()-1`` to "A2:A" using ``setFormula()``. The document of setFormula() is [here](https://developers.google.com/apps-script/reference/spreadsheet/range#setformulaformula). If your script doesn't work, please add it including the detail information to your question. By this, it will help users think of your solution. – Tanaike Jul 16 '18 at 23:03
  • @Rubén I have made this clear by giving a GAS tag to my question. I have made some efforts and will include my code soon. Thanks. – Omid Jul 17 '18 at 10:03
  • @Tanaike Thanks. It seems a nice idea. I have made some efforts and will include my code soon. – Omid Jul 17 '18 at 10:03

2 Answers2

0
  1. In Cell A2: enter your starting value 1
  2. Cell A3: type =A2+1
  3. Click on A3, and you will see a small square in the bottom right corner of A3
  4. Click and drag that little square down as far as you need the numbers
  5. When you release the mouse button, it will auto fill the series of numbers
Rubén
  • 34,714
  • 9
  • 70
  • 166
Matthew
  • 16
  • 2
  • 1
    Many users doesn't like that SO be referred as a forum as the workings of this site are very different from many "other" forums, like posts should not include notes like `(sorry...` as they are considered "noise". – Rubén Jul 16 '18 at 17:18
  • Thanks for your answer. I am trying to do this by GAS. I have made some efforts and will include my code soon. – Omid Jul 17 '18 at 10:02
0

Finally I made it!

I wrote the following two functions for this purpose. Creating the following functions took me a lot of time and energy and a lot of trial and error, but it was really enjoyable.

The main GAS function with passing arguments:

function refreshRefNums(sheetName,refColumnIndex,refFirstRowIndex){
  var spreadSheet = SpreadsheetApp.getActive();
  var sheet = spreadSheet.getSheetByName(sheetName);
  var refLastRowIndex = sheet.getLastRow();
  var baseRange = sheet.getRange(refFirstRowIndex,refColumnIndex,2);
  var destinationRange = sheet.getRange(refFirstRowIndex,refColumnIndex,refLastRowIndex);
    baseRange.autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
}

And the function I run to do the job:

function refreshRefNumsIOUData(){
  refreshRefNums("IOU Data",1,2);
}

Notes:

sheetName is the name of the sheet that has a column for unique reference numbers strating from 1.

refColumnIndex is the index of the column that contains reference numbers.

refFirstRowIndex is the index of the row that reference numbers start.

refLastRowIndex is the index of the last row containing reference numbers.

Omid
  • 177
  • 2
  • 2
  • 10