How do I figure out the starting and ending Row/Column for data on a spreadsheet?

asked 2021-05-09 16:56:48 +0200

vek.m1234 gravatar image

I want to write a Macro to iterate my cells on the SpreadSheet and set background colors for the cells based on some criteria. My understanding is I have to first manually/hardwire/specify the interested range of cells. oRange = enSheet.getCellRangeByName("A2:C6")

Does the sheet have properties that could be useful vs randomly specifying a large range to grab a large address-space and then iterating to look for text and heuristically determining my row/columns/address-space.

I tried Print oRange.StartColumn, oRange.EndColumn but that didn't work and Xray oRange did not display anything relevant.. except ColumnLabelRanges

edit retag flag offensive close merge delete

Comments

.... and set background colors for the cells based on some criteria.

Just for curiosity: Why don't you consider to use Conditional Formatting for that task?

Opaque gravatar imageOpaque ( 2021-05-09 17:56:47 +0200 )edit

https://i.imgur.com/sgur1lC.png so this is my sheet currently - I want to color vegetables one color, dairy a different color and so on AND I DON'T want to do it manually.. veggie = [ 'eggplant', 'carrot' ] color = { 'veggie' : 'blue' } I can't do data structures using conditional formatting and then I would need to use regex for match(r'egg+', Sheet1.A1)

vek.m1234 gravatar imagevek.m1234 ( 2021-05-09 18:23:22 +0200 )edit

on AND I DON'T want to do it manually.

Conditional Formatting is not manual, but automatic formatting of cells based on conditions (to be implemeted once). I don't see how this could be easily setup using macros especially when it is not clear how your questions should support this task. Of course a macro could evaluate .StartColum,.StartRow, .EndColumn and .EndRow of either UsedArea or CurrentSelection but i don't see what it's worth for.

Sub UsedArea

 Dim oDoc       As Object
 Dim oSheet     As Object
 Dim oCursor    As Object
 Dim oCell      As Object

 oDoc = ThisComponent
 oSheet = oDoc.CurrentController.ActiveSheet

 oCursor = oSheet.createCursor()
 oCursor.gotoStartOfUsedArea(False)
 oCursor.gotoEndOfUsedArea(True)

 StartCol=oCursor.RangeAddress.StartColumn
 StartRow=oCursor.RangeAddress.StartRow
 EndCol=oCursor.RangeAddress.EndColumn
 EndRow=oCursor.RangeAddress.EndRow

End Sub
Opaque gravatar imageOpaque ( 2021-05-09 18:55:57 +0200 )edit

How do you create a conditional formatting rule for a long list of items - can you use AND OR NOT or regex-patterns: I cannot write "egg" OR "milk" as a condition for "contains" It seems to be very basic for numbers mostly: less than 10 (ah! thank you - UsedArea)

vek.m1234 gravatar imagevek.m1234 ( 2021-05-09 19:29:50 +0200 )edit