Calc : Repeat a formula for all rows in a new column.

[maaadbob]

Hi All,
After importing a CSV file, I have numerical data in columns. In the next available column, I perform a calculation with data from some of the previous columns.
Once the formula is entered, I’d normally just grab the bottom right corner of the cell and drag down for all the imported rows; so I have my calculated column.
This hasn’t been a problem, but recently I have datasets that are 30,000 rows long (tall?).
Is there a quick way to select a cell and have LO replicate it and its formula for all the rows imported (performing the drag that I used to do manually) ?

Cheers,
Bob.

[keme]

Some previous suggestions, and a new one. There are advantages and limitations to them all:

The explicit range addressing (as suggested by @anon73440385 ) works, but you need to know the length of your dataset.

Shift+Ctrl+End (as suggested by @LeroyG ) works, unless you have content below or to the right of your table.

Double click on the fill handle (bottom right of selected cell) works, but requires that all rows have data in the last column before the one to be filled. The fill stops if there is an empty cell.

[LeroyG]

Given the right conditions, “double click on the fill handle” seems to me the best answer.

[anon73440385]

Hello,

perform the following:

• Enter the formula into the first cell (e.g. C1)
• Now enter C1:C30000 into the name box (left of the formula bar) and press ENTER- all 30000 row get selected
• Type CTRL+D

Hope that helps.

If the answer helped to solve your problem, please click the check mark () next to the answer.

[LeroyG]

maaadbob,

A simple solution if the formula is on the last column used:

• Input the formula into the first cell
• Copy the first cell
• Press Ctrl+Shift+End (I am asuming you are using Windows)
• Paste

Following @anon73440385 example, a similar aproach could be:

• Type C1:C30000 into the name box and press ENTER
• Type the formula (would appear into the first cell)
• Press Alt+Enter

[daveq]

Sheet/Fill/Down: Fills your selected range of at least two rows with the contents of the top cell(s) of the range.

[Lupp]

Since the topic meanwhile got >1 kView a task of the kind seems to occur frequently.
I therefore now post user code for it also covering cases where the “guidecolumn” may contain empty cells or is not adjacent to the new column(s).

The source range (your new formulas e.g.) may be any single range (more than one column e.g, if more than one row to the same effect as if you do an autofill by Ctrl+Drag).

To avoid nasty surprises the action will only be carried out if the range to get filled has no true content (number, date, text, formula) in advance. Anchored objects -including annotations- remain untouched.
The target range extends down to the end of the “used area” of the sheet. The term includes cells with ‘different attributes’, (I have to admit that I don’t know an exact specification for the term “used area”. Play with Ctrl+End to check yourselft.)

Sub fillDownForUsedAreaRows(Optional pSourceRange As Object)
REM The Sub creates as a single undo step.
REM Formatting in the fillRange will be replaced by formatting of the sourceRange!
If IsMissing(pSourceRange) Then pSourceRange = ThisComponent.CurrentSelection
If NOT pSourceRange.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub
sourceLength = pSourceRange.Rows.Count
sheet = pSourceRange.Spreadsheet
sheetCur = sheet.CreateCursor()
sheetCur.gotoEndOfUsedArea(False)
uaEndRow = sheetCur.RangeAddress.EndRow
With pSourceRange.RangeAddress
  If (.EndRow>uaEndRow) OR (uaEndRow=sheet.RangeAddress.endRow) Then Exit Sub
  fillRange = sheet.getCellRangeByPosition(.StartColumn, .EndRow + 1, .EndColumn, uaEndRow)
  check = fillRange.queryContentCells(23) REM = number(1)+Date(2)+string(4)+formula(16)
  If check.Count>0 Then Exit Sub
  fillRange = sheet.getCellRangeByPosition(.StartColumn, .StartRow, .EndColumn, uaEndRow)
End With
fillRange.fillAuto(0, sourceLength) REM 0 = com.sun.star.sheet.FillDirection.TO_BOTTOM
End Sub