How to find all cell addresses (specified ranges) with annotations in a sheet with macro?

[Jonjozz]

Is there a command that quickly finds all the cells with comments (annotations) in a spreadsheet e displays their addresses? I know that the code “Range.queryContentCells( 8 ).Count” counts all the cells with comments in a range.

[JohnSUN]

Do you mean something like this?

Function getCommentRng(incell As String) As Variant
Dim oSheets As Variant 
Dim oCellRangesByName As Variant 
Dim oRange As Variant 
Dim oQueryResult As Variant 
Dim oCell As Variant 
Dim i As Long 
Dim oAnnotation As Variant 
	On Error Resume Next 
	oSheets = ThisComponent.getSheets() 
	oCellRangesByName = oSheets.getCellRangesByName(incell) 
Rem Of course, you can process not the first of the specified ranges, but all of them - this is just an example of use
	oRange = oCellRangesByName(0)
	oQueryResult = oRange.queryContentCells( 8 )
	ReDim oAnnotation(0 To oQueryResult.getCount()+1, 0 To 1)
	oAnnotation(UBound(oAnnotation,1)-1,0) = "---"
	oAnnotation(UBound(oAnnotation,1)-1,1) = "---"
	oAnnotation(UBound(oAnnotation,1),0) = ""
	oAnnotation(UBound(oAnnotation,1),1) = ""
	i = -1
	For Each oCell In oQueryResult
		i = i + 1
		oAnnotation(i,0) = oCell.AbsoluteName
		oAnnotation(i,1) = oCell.getAnnotation().getString()
	Next oCell
	getCommentRng = oAnnotation
End Function

Call it as `{=GETCOMMENTRNG(“A1:B8”)}’

[fpy]

navigating in iterators :
Can I export/print a list of the comments in a Calc spreadsheet?
Copy simple text from both values and comments (.ods file) an maintain pairing? - #6 by karolus

[Lupp]

A sloppy variant:

Sub findDescribeSelect()
cCtrl   = ThisComponent.CurrentController
cSheet  = cCtrl.ActiveSheet
annoRgs = cSheet.queryContentCells(8)
out     = annoRgs.RangeAddressesAsString
out     = Join(Split(out, ";"), Chr(10))
MsgBox(out)
cCtrl.select(annoRgs)
End Sub

See attached example:_
disask121346AnnotationFinder.ods (12.9 KB)

[Jonjozz]

(post deleted by author)

[Jonjozz]

Simple, beautiful, and elegant…Thanks a million