I need please a formula that counts empty cells and fill them with consecutive numbers before between and after filled cells(AA or any other text ).
An example in the picture.
Please, share another screenshot with what do you want. Thanks.
Impossible with a regular formula. Maybe a job for a macro.
Whatever works is great for me.
A macro would do the magic then so be it.
Welcome!
Try this:
- Create an auxiliary sheet.
- Fill in column A with the value 1.
- In cell B1, enter the formula
=N(A1)+1 - Expand the cell to the right and down with a little margin
- Return to the data sheet, select the entire range and copy it to the clipboard.
- Go to the auxiliary sheet in cell A1 and use the Paste Special Ctrl+Shift+V - Skip empty cells.
- Copy the current selection and return to the data sheet.
- Use Paste Special - Values Only
- Delete the help sheet.
3 Likes
could’nt replicate your instructions.
maybe you can record a whole video and post it for me?
This will take some time, please wait - I’ll let you know when it’s ready. The video will be on YouTube - you can not publish large files here
Thank you.
Did’nt work for me but thank you very much for all your efforts.
Much appreciated.
Good (actually bad, but if it didn’t work out, then I didn’t explain something well).
Try this code:
Sub enumerateBlankCells()
Dim oSheet As Variant
Dim oCursor As Variant
Dim aCellAddress As New com.sun.star.table.CellAddress
Dim oConversion As Object
Dim oDataArray As Variant
Dim oFormulaArray As Variant
Dim iColumn As Long, iRow As Long
oSheet = ThisComponent.getCurrentController().getActiveSheet()
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(True)
oDataArray = oCursor.getDataArray()
oFormulaArray = oCursor.getFormulaArray()
aCellAddress = oCursor.getCellByPosition(0, 0).getCellAddress()
oConversion = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
For iRow = LBound(oDataArray) To UBound(oDataArray)
For iColumn = LBound(oDataArray(iRow)) To UBound(oDataArray(iRow))
If Trim(oDataArray(iRow)(iColumn)) = "" Then
If iColumn = 0 Then
oFormulaArray(iRow)(iColumn) = "1"
Else
aCellAddress.Column = iColumn - 1
aCellAddress.Row = iRow
oConversion.Address = aCellAddress
oFormulaArray(iRow)(iColumn) = "=N(" & oConversion.UserInterfaceRepresentation & ")+1"
EndIf
EndIf
Next iColumn
Next iRow
oCursor.setFormulaArray(oFormulaArray)
End Sub
Example spreadsheet with this macro - FillEmptyCellsNumbers.ods (11.9 KB)
WOW…
Worked like a charm.
Thank you very much sir.
Your explanation and photos were fine - the question is at wich step “it didn’t work”. One possibility is the function, if the locale is not english…
1 Like
def enumerate_empty_row_ranges():
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(True)
col_size, row_size = cursor.Columns.Count, cursor.Rows.Count
cursor.collapseToSize(col_size,1)
for _ in range(row_size):
for empty in cursor.queryEmptyCells():
empty.DataArray = [list(range(1,empty.Columns.Count+1))]
cursor.gotoOffset(0,1)
@karolus Yes, I was also thinking about .queryEmptyCells(). But then I realized that I don’t know anything about the OP’s data. If the “blank cells” in his table are the result of a formula that returns an empty string, then this script will do nothing.