[Solved] Copy A1 to first empty cell in column B

[raciret]

Hello,

I'm new in using OpenOffice Calc. I'm creating a spreadsheet and I want to copy the value of "A1" and paste it to the last blank/empty cell in column B.

I found some codes that finds the last non-empty cell but cannot find one that would find the last empty cell, and that can work if only I could insert a "Row + 1" like in Excel.

Any help would be appreciated. Thank you!

[Zizi64]

I found some codes that finds the last non-empty cell but cannot find one that would find the last empty cell,

The last empty cell in a row located in the 1,073,741,824-th row. (I can not imagine if you need insert some data into the 1,073,741,824-row: therefore it is will be empty, and it is the last cell ...

...Or do you need find the FIRST empty cell? Or do you need find the last empty cell in a non empty cell range?

[raciret]

Yes, I need the last empty cell in a non empty cell range.

[Zizi64]

As far as I know, you can not fint it by Find & Replace function. An empty cell never matches anything.
But you can use a helper column, and some formulas on the non empty cellrange in the column C:

Code: Select all

IF(ISBLANK(B1);ROW();"")

And then you need copy/paste the formula into the non empty cellrange.

Then you can find the maximum of the displayed row numbers by the MAX() function.

[Zizi64]

Or you can write (not to record it) a short macro code to achieve this task. The macro will paste the content of the clipboard (or the content of the cell A1) into the last empty cell of a selected (or a given) cellrange in a specific column. A cyclic macro can find the empty cells in a specific cellrange.

[Lupp]

What about

Code: Select all

=HYPERLINK("#"&ADDRESS(SUMPRODUCT(MAX(ROW(B2:B31)*ISBLANK(B2:B31)));COLUMN(B2)))

in C1 e.g.?
This formula will not only find the address of the last blank cell within the range B2:B31 (part of a column!), but also create a link to jump to that cell (select it).

For application in BASIC code you have to pass the parameters, of course (always a problem for 'Sub'), and to use a function access object for the Calc functions. The HYPERLINK function will be omitted then.

[MrProgrammer]

I want to copy the value of "A1" and paste it to the last blank/empty cell in column B.

The simple way is to copy the value, use Lupp's HYPERLINK function, then paste. If you want a macro, read Help → Index → shortcut keys;spreadsheets. You don't need to write a macro. You can record one with Tools → Macros → Record. Here is what I got:

sub T83777
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = false

dispatcher.executeDispatch(document, ".uno:GoToStart", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args5())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Sel"
args7(0).Value = false

dispatcher.executeDispatch(document, ".uno:GoToStart", "", 0, args7())


end sub