We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

calc - loop through all cells in the current selection

asked 2020-02-10 06:31:42 +0200

stabiloboss gravatar image


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.


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-12-24 12:26:50 +0200

Lupp gravatar image

updated 2020-12-24 12:41:16 +0200

(In addition to what @igorlius altready posted:)

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
sel =ThisComponent.CurrentSelection
If sel.supportsService("com.sun.star.sheet.SheetCellRanges") Then
  rgs = sel
  If NOT sel.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub
  rgs = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
  rgs.addRangeAddress(sel.RangeAddress, False)
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
edit flag offensive delete link more


thank you!

stabiloboss gravatar imagestabiloboss ( 2020-12-25 04:37:05 +0200 )edit

answered 2020-12-23 14:47:24 +0200

igorlius gravatar image

updated 2020-12-24 09:45:35 +0200

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
end Sub

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

Hope it helps.

edit flag offensive delete link more



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

stabiloboss gravatar imagestabiloboss ( 2020-12-24 08:49:01 +0200 )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.

stabiloboss gravatar imagestabiloboss ( 2020-12-25 04:36:54 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2020-02-10 06:31:42 +0200

Seen: 742 times

Last updated: Dec 24 '20