Iterate over all non-empty cells on the sheet (basic)?

[dan11]

In basic is there any way to get all non-empty cells on the sheet?

All questions I found asked this question for some predefined cell selection/range. I’m trying to iterate over only cells that exist throughout the entire sheet?

[Villeroy]

Install the MRI extension and find out.
https://forum.openoffice.org/en/forum/viewtopic.php?t=49294

[Lupp]

(Was done by direct editing without traces. My comment is now partly irrelevant.)
I think you should make clear if you are talking about one “entire sheet” or about the “all non-empty cells in the document”.
In addition there may be different concepts concerning what “non-empty cells” are. After all there are formats, annotations, shape anchors …

[dan11]

Apologies, I’ve corrected the question.

[Lupp]

No harm done!
Another visitor to the thread may wonder why JohnSUN goes through all the sheets - and so on.
The visitor will understand now.

[Villeroy]

May be you want to use Apache OpenOffice Community Forum - Merge sheets of one spreadsheet document - (View topic) which copies the used areas of selected non-hidden sheets to the current region around a cell named “Target” skipping a specified amount of header rows. It does not simply copy cells into the void area of the target sheet. It inserts new rows in order to update all references pointing to the target area (formulas, names, charts, …).
Referring to your other topic, you install the macro and configure the macro by definition of 2 named cells “Headers” and “Target”. Then you update the external reference to the csv file, select that sheet and call the macro.

[JohnSUN]

Please see XCellRangesQuery Interface Reference and CellFlags Constant Group Reference. Further simply:

Sub EnumerateAllNonEmptyCells()
Dim oSheets As Variant, oSheet As Variant, nSheet As Integer 
Dim oAllRanges As Variant, oCells As Variant, oCell As Variant
	oSheets = ThisComponent.getSheets()
	For nSheet = 0 To oSheets.getCount()-1
		oSheet = oSheets.getByIndex(nSheet)
		oAllRanges = oSheet.queryContentCells( _
								com.sun.star.sheet.CellFlags.VALUE + _
								com.sun.star.sheet.CellFlags.DATETIME + _
								com.sun.star.sheet.CellFlags.STRING + _
								com.sun.star.sheet.CellFlags.FORMULA)
		oCells = oAllRanges.getCells()
		For Each oCell In oCells
			Print oCell.AbsoluteName & " = '" & oCell.getString() & "'"
		Next oCell
	Next nSheet
End Sub

[dan11]

Perfect, thank you.