Creating dynamic name range in libreoffice
A little bit more context maybe?
Like Excel 
I have a calc spreedsheet with three columns;A B and C
Column A header is ,players , B scores, C dates. My current name range is $Sheet1.$A$1:$C$27.
Next week the range will be A1: C47, the following week additional rows will be added…
I would like to make the range dynamic. To avoid having to manual update the range every week. I have tried using offset formula to no avail.
Also a line of code to enter in a macro to accomplish the same task would also be helpful.
For now i am desperate for a solution for the dynamic name range.
Thanks to all inadvance
Be sure that Tools>Options>Calc>General>"Expand references when new columns/rows are inserted is checked.
Insert cells for new data anywhere within the list range or directly below. Cell insertion updates all kinds of references in formulas, names, database ranges, validation, conditional formatting, charts and listboxes.
Without the above mentioned option, insertion at the first row will move the reference down, insertion directly below will not expand any reference, only insertion between the second and last row will expand references.
I am trying to
I tried using this formula in LO to define a dynamic name range it did not work.
Would you evaluate the formula, establish why it doesn’t work?
Thanks.
=offset($sheet1.$A$2,0,0,Counta($sheet1.$A:$A),1).
I totally understand Excel and LO is two different Coding
is it possible to convert the code below to LO basic
Part 1 (create the name range “TeamPlayers” based on the number of names in column A)
Sheet2.Activate ‘’’’’’’’‘Players Scores
Sheet2.Range(“a6:a” & Cells(Rows.Count, “A”).End(xlUp).Row).Name = “TeamPlayers”
Villeroy,
Thanks for your answer, however it doesn’t work for my situation.
The scores are copy from a weekly score sheet via macro and pasted to the next available row at the end of Score database. I don’t know how to paste in the middle of the score database with the current macro.
Hi @Jcrowrab , I do it like this: I name the data area with a few more lines and in the last line I leave it with a black background color, to alert me about the end of the data. When I need to include more data, I insert enough lines before the last black one, this way the named area is updated.
You can check “Insert cells” in the paste-special dialog, paste “values only” and the formatting inherits from the row above. This gives a consistent formatting based on the preferences set up for the target document while expanding all references.
Next thing I can offer is [Calc, Python] Expand/shrink list ranges which inserts new rows based on a “sloppy selection” and copies down any formulas from the above row. However, you should select the right count of rows before calling the macro. Possibly, someone can contribute a code snippet do insert new rows based on clipboard content.
Like 90% of all spreadsheet users you try to avoid databases where any table size defined by its predefined columns and the amount of records stored in the table. Formatting is not even stored in a database. Formatting is applied by the final medium where the data are used (report, text, spreadsheet, web page).
sub Main
Doc = thiscomponent
namedranges = Doc.NamedRanges
myrange = namedranges.getByName("TeamPlayers")
oSheet= Doc.Sheets("Sheet2")
oColumn=oSheet.Columns.getByName("A")
oContent=oColumn.queryContentCells(1+4) ' value + string in Column A
p=oContent.RangeAddresses
r = p(ubound(p)).EndRow + 1
myrange.setContent("$Sheet2.$A$1:$A$"+r)
End SubSub duplicateContent
sh = thiscomponent.sheets.getByName("Sheet2")
cell = sh.getCellByPosition(0,0)
rg = getCurrentRegion(cell)
adr = rg.getRangeAddress()
data = rg.getDataArray()
rg2 = sh.getCellRangeByPosition(adr.StartColumn, adr.EndRow +1, adr.EndColumn, 2* adr.EndRow +1)
sh.insertCells(rg2.getRangeAddress(),com.sun.star.sheet.CellInsertMode.DOWN)
rg2 = sh.getCellRangeByPosition(adr.StartColumn, adr.EndRow +1, adr.EndColumn, 2* adr.EndRow +1)
rg2.setDataArray(data)
End sub
Some helper functions
Function getCurrentRegion(oRange)
Dim oCursor
oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
oCursor.collapseToCurrentRegion
getCurrentRegion = oCursor
End Function
Function getUsedRange(oSheet)
Dim oCursor1, oCursor2, addr
oCursor1 = oSheet.createCursorByRange(oSheet)
oCursor1.gotoStart()
addr = oCursor1.getRangeAddress()
oCursor2 = oSheet.createCursorByRange(oSheet)
oCursor2.gotoEnd()
addr.EndColumn = oCursor2.RangeAddress.EndColumn
addr.EndRow = oCursor2.RangeAddress.EndRow
getUsedRange = oSheet.getCellRangeByPosition(addr.StartColumn, addr.StartRow, addr.EndColumn, addr.EndRow)
End Function
Function getUsedRange2(oSheet)
REM includes formatted blanks
Dim oCursor
oCursor = oSheet.createCursor()
oCursor.gotoStartOfUsedArea(False)
oCursor.gotoEndOfUsedArea(True)
getUsedRange2 = oCursor
End Function
Function getActiveCell(Optional oView )
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
If isMissing(oView) then oView = ThisComponent.getCurrentController()
as1() = Split(oView.ViewData, ";")
lSheet = CLng(as1(1))
sDum = as1(lSheet +3)
as1() = Split(sDum, "/")
on error goto errSlash
lCol = CLng(as1(0))
lRow = CLng(as1(1))
on error goto 0
getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
if NOT(bErr) then
bErr = True
as1() = Split(sDum, "+")
resume
endif
End Function
Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
addr = oRg.getRangeAddress()
addr.StartRow = addr.StartRow + nRowOffset
addr.EndRow = addr.EndRow + nRowOffset
addr.StartColumn = addr.StartColumn + nColOffset
addr.EndColumn = addr.EndColumn + nColOffset
if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function
'pass a spreadsheet-document, sheet or range together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
REM use the sheet specified by given address
oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
else
REM use given object (range/sheet) as parent range
oSheet = obj
endif
getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
getRangeByAddress = Null
End Function
Or without all that silly code:
Select the range and copy.
Hit Ctrl++ to insert cells and confirm “Shift down”
Paste.
All references expanded.
A really BIG THANK YOU to all who help me solved an issue i have work on for three weeks
Thank you ALL
You Guys are GREAT
The macro works perfect if applied to a single sheet. When used on several sheets with different parameters (new parameters set for each sheet) the range always revert back to the first sheet used.
Of course! The script was written specifically for this task, that’s exactly how it was designed, that’s exactly what it does.
Sub resizeAllNamedRanges()
Dim oSheets As Variant, oSheet As Variant
Dim oNamedRanges As Variant, oNamedRange As Variant
Dim i As Long, j As Long
Dim oReferredCells As Variant, oTopLeftCell As Variant, oCursor As Variant
Dim sContent As String, aContent As Variant
Rem First, let's update the local ranges:
Rem Let's go through all the sheets of the spreadsheet and, if we find local ranges on them, update this if necessary
oSheets = ThisComponent.getSheets()
For i = 0 To oSheets.getCount()-1
oSheet = oSheets.getByIndex(i)
oNamedRanges = oSheet.NamedRanges
For j = 0 To oNamedRanges.getCount()-1
oNamedRange = oNamedRanges.getByIndex(j)
oReferredCells = oNamedRange.getReferredCells()
Rem If a named range contains not a reference to a range of cells, but a formula, then its ReferredCells will be Null:
If Not IsNull(oReferredCells) Then
oTopLeftCell = oReferredCells.getCellByPosition(0, 0)
Rem Here, to create the cursor, we use the sheet we are currently working with:
oCursor = oSheet.createCursorByRange(oTopLeftCell)
oCursor.collapseToCurrentRegion()
If oReferredCells.getRangeAddress().EndRow <> oCursor.getRangeAddress().EndRow Then
Rem If the last row number in a continuous data range is not the same as in a named range,
Rem change the last row number in the Content of the named range:
sContent = oNamedRange.getContent()
aContent = Split(sContent,"$")
aContent(UBound(aContent)) = oCursor.getRangeAddress().EndRow+1
oNamedRange.setContent(Join(aContent,"$"))
EndIf
EndIf
Next j
Next i
Rem Now let's process the spreadsheet's global named ranges:
oNamedRanges = ThisComponent.NamedRanges
For j = 0 To oNamedRanges.getCount()-1
oNamedRange = oNamedRanges.getByIndex(j)
oReferredCells = oNamedRange.getReferredCells()
If Not IsNull(oReferredCells) Then
oTopLeftCell = oReferredCells.getCellByPosition(0, 0)
Rem Here, to create a cursor, we use the sheet on which this named range is located:
oCursor = oReferredCells.getSpreadsheet().createCursorByRange(oTopLeftCell)
oCursor.collapseToCurrentRegion()
If oReferredCells.getRangeAddress().EndRow <> oCursor.getRangeAddress().EndRow Then
sContent = oNamedRange.getContent()
aContent = Split(sContent,"$")
aContent(UBound(aContent)) = oCursor.getRangeAddress().EndRow+1
oNamedRange.setContent(Join(aContent,"$"))
EndIf
EndIf
Next j
End Sub
Once again thanks for the help. Your help has help me a great deal in learning and writing code. This would not have been possible without you guy’s help.
THANKS!
Ive been able to expand my range by placing in the last row a dummy file, ej: XXXX.
Define your range. Add new data to it, using a macro insert a row after your first data line, then sort.
The new row we be inserted inside your range.
If it doesn’t matter the placement of the new row, there is no problem. If you needed it placed at the end of the range you’ll need to sort by another column to accomplish that.
Im using a date column and it works