macro named range delete/create named range last used row cell containing last balance erase named range create named range Ennio-Sr Andrew Douglas Pitonyak Marc Santhoff Sasa Kelesevic Reassign named range to a cell in last used row

How to determine last row used and set a named range for a cell in that row

GregChi <geardoc36@snet.net>; asked a similar question on users@openoffice.org

and received workarounds more than a direct answer. I had a similar problem and after

reading Andrew Pitonyak's "Useful Macro Information for OOo" and asking a few questions

on dev@api.openoffice.org, wrote a macro which does the job. It considers col. F as the one

containing a formula to determine current account balance (i.e. previous balance , plus credit,

minus debit). Col. headings (A to F) are: Date, Value_date, Description, Debit, Credit, Balance).

Once you have copy pasted the code in your Calc file, you can well assign the macro

(_0_create_last_bal) to the Event/Opening of document so that it is run when the file

is opened. A second macro (create_last_bal) mentioned in the dialog box, does the same

as it just calls the previous one.

sub _0_create_last_bal ' ---------------------------- ' On error exit On Error goto Label ' define variables Dim oDoc As Object ' The spreadsheet we are working on Dim oSheet As Object ' The sheet (usually first one) Dim oCell As Object ' The particular cell we select Dim oRange ' The created range Dim oRanges ' All named ranges Dim sName$ ' Name of the named range to create msg$ = "I' m ready to accept new records: for each new one copy formula " +_ "in last balance cell (or run macro 'create_new_rec')." oDoc=ThisComponent ocell=ThisComponent.CurrentSelection oRanges = ThisComponent.NamedRanges sName$="last_bal" ' Here we verify whether there is already a range with that name and, ' in the affirmative, erase it so that the new one can be created: If oRanges.hasByName(sName$) Then oRanges.getByName(sName$) oRanges.removeByName(sName$) End If ' We go to the last cell on the desired column odoc = ThisComponent.CurrentController.Frame dispatcher = createUnoService("{@see com.sun.star.frame.DispatchHelper}") Dim args1(0) as new {@see com.sun.star.beans.PropertyValue} ' We save the file to make sure what is displayed is read correctly ' SEEMS NOT NECESSARY dispatcher.executeDispatch(oDoc, ".uno:Save", "", 0, array()) args1(0).Name = "ToPoint" args1(0).Value = "$F$6" dispatcher.executeDispatch(oDoc, ".uno:GoToCell", "", 0, args1()) dispatcher.executeDispatch(oDoc, ".uno:GoDownToEndOfData", "", 0, args1()) ' and check whether its value is zero ocell=ThisComponent.CurrentSelection ' gets value of new current cell vlc = oCell.getValue() ' get value of selected cell (last cell) if vlc = 0 then dispatcher.executeDispatch(oDoc, ".uno:Cut", "", 0, args1()) dispatcher.executeDispatch(oDoc, ".uno:GoUp", "", 0, args1()) ' we determine the row number of the selected cell: ' numbering starts from 0, so we need add 1: r$ = oCell.CellAddress.row+1 xcl$ = "Sheet1.$F$"+r$ ' ref to col F may be changed else ' This is a special case, so we need add 2 r$ = oCell.CellAddress.row+2 xcl$ = "Sheet1.$F$"+r$ ' ref to col F may be changed End if ' we set our named range name: Dim oCellAddress As new {@see com.sun.star.table.CellAddress} oCellAddress.Sheet = 0 ' The first sheet oRanges.addNewByName(sName$,xcl$,oCellAddress,0) ' We arrange for copying the formula from last used row to the next one dispatcher.executeDispatch(oDoc, ".uno:Copy", "", 0, Args1()) Dim args2(1) as new {@see com.sun.star.beans.PropertyValue} args2(0).Name = "By" args2(0).Value = 1 args2(1).Name = "Sel" args2(1).Value = false dispatcher.executeDispatch(oDoc, ".uno:GoDown", "", 0, args2()) dispatcher.executeDispatch(oDoc, ".uno:Paste", "", 0, Args2()) dispatcher.executeDispatch(oDoc, ".uno:GoToStartOfRow", "", 0, args2()) Print msg$ Exit sub Label: print "Error!" Exit Sub End Sub Rem ################################################################### Sub create_new_rec ' we call the previous macro: sub _0_create_last_bal End Sub ' #####################################################################

Version 2.0 I discovered some flaws in the initial version (on some circumstances "last_bal" was created on the last but one row). So it was necessary to re-write the code. The file structure is very simple (as said above). You can put this formula in cell F6: =IF(ISBLANK(B6);0;F5-D6+E6).