# calc - loop through all cells in the current selection

Hi,

I need a macro (in basic) to loop through all the cells in the current selection. This selection could have been made manually or with code.

This sounds really basic, but I haven't been able to find the answer. So how do I do this?

I also would like to know where I should have found the answer myself, but I will ask this question in a different post.

Thanks!

edit retag close merge delete

Sort by » oldest newest most voted

The CurrentSelection of a spreadsheet document is'nt always an object of the same type (supporting the same services). If nothing explicitly is selected, the cell having the keyboard focus is returned. You also get a single cell returned if one cell is selected (shown highlighted). The focus isn't evaluated now. In both these cases the service com.sun.star.sheet.SheetCellRange is also supported by the selection, and the code suggested by @igorlius can work with only the value 0 for i and for j as well. If a single range of more than one cell is selected: No problem, that's the standard case.

If more than one single range is selected, the mentioned service will not be supported. The relevant service you need to rely on now is com.sun.star.sheet.SheetCellRanges (Regard the plural-s.)

If you want code also working with such a selection, you can use:

Sub loopThroughSingleCellsOfTheCurrentSelection()
REM The CurrentSelection will even contain the stacked ranges
REM of additional sheets IF MORE THAN ONE SHEET IS SELECTED.
sel =ThisComponent.CurrentSelection
If sel.supportsService("com.sun.star.sheet.SheetCellRanges") Then
rgs = sel
Else
If NOT sel.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub
rgs = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
End If
For Each rg In rgs
uR = rg.Rows.Count - 1
uC = rg.Columns.Count - 1
For r = 0 To uR
For c = 0 To uC
rcCell = rg.getCellByPosition(c, r)
REM Do here whatever you want to do with the single cell. Example:
Print rcCell.AbsoluteName
Next c
Next r
Next rg
End Sub

more

thank you!

( 2020-12-25 04:37:05 +0100 )edit

Most likely to late, but

these might be help/usefull for you

The odt contains extensive examples for looping through cell ranges.

Update, since this still seems to be of interesst, here is the macro:

Sub LoopSelection
Set oRange = ThisComponent.CurrentSelection
For i = 0 To oRange.Rows.getCount() - 1
For j = 0 To oRange.Columns.getCount() - 1
Set oCell = oRange.getCellByPosition( j, i )
Print oCell.String
Next
Next
end Sub


If you need any further help, just ask in the comment.

Hope it helps.

more

1

thank you very much, for the macro and for the links!

( 2020-12-24 08:49:01 +0100 )edit

Hmm, I'm not sure what the proper course of action is on this forum, but I think I should accept Lupp's answer as it covers a wider range of situations. i hope I'm not offending.

( 2020-12-25 04:36:54 +0100 )edit