[Solved] Macro For Row Height
[Solved] Macro For Row Height
I need a macro that will change row height based on character counts in cells. Say cell b
3 and various other cells in column b have less than 20 characters in them, then they would be formated to .2 inches high.
3 and various other cells in column b have less than 20 characters in them, then they would be formated to .2 inches high.
Last edited by alkaline5 on Tue Nov 15, 2016 2:59 am, edited 1 time in total.
LibreOffice 4.2 on Linux
Re: Macro For Row Height
Here is a simplistic macro that sets the height of row 2 to 0.2 inches if B2 has fewer than 20 characters and to 0.4 inches otherwise.
Do you really need a macro? You can set the cell text to wrap automatically, which I find to be more useful.
Code: Select all
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByName("Sheet1")
oB2 = oSheet.getCellrangeByName("B2")
StrLeng = LEN(oB2.String)
oRow = oB2.getRows()
If StrLeng < 20 Then
oRow.Height = 0.2 * 25.4 * 100 'measured in 1/100 mm
Else
oRow.Height = 0.4 * 25.4 * 100
End if
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macro For Row Height
Well, thanks for what looks like a partial answer. But the situation would be more involved I think, with the macro finding not one row but many amongst about 5000 rows.
LibreOffice 4.2 on Linux
Re: Macro For Row Height
Of course it is a partial answer, you have given a very sketchy description of what needs to be done. For the cells to affect, your original posts just mentions "cell b3 and various other cells in column b" and the only action mentioned is that < 20 characters produces a row height of 0.2". I invented setting the row height to 0.4 just to do something should the number of characters be >= 20. You need to define which cells need to be inspected and what the relationship should be between the number of characters and the row height.
I guessed that you intended to look at a few specific cells, so I used the getCellRangeByName() method but now I suspect getCellByPosition(nColumn, nRow) would be better, if the range of cells is continuous. Row and columns are indexed from 0, so column B has an index of 1. All you have to do is substitute getCellByPosition() for getCellrangeByName(), code the height/character length relationship and use a loop to step through the cells.
Again, why won't setting the cells to wrap text automatically work?
I guessed that you intended to look at a few specific cells, so I used the getCellRangeByName() method but now I suspect getCellByPosition(nColumn, nRow) would be better, if the range of cells is continuous. Row and columns are indexed from 0, so column B has an index of 1. All you have to do is substitute getCellByPosition() for getCellrangeByName(), code the height/character length relationship and use a loop to step through the cells.
Again, why won't setting the cells to wrap text automatically work?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macro For Row Height
Well, in VBA, I think it looks something like this:
Code: Select all
Sub RowHeight ()
For i = 1 To 5000 Step 1
If Len(Cells(i, 1)) > 20 Then
Rows(i).RowHeight = 20
End If
Next if
End Sub
LibreOffice 4.2 on Linux
Re: Macro For Row Height
And in OpenOffice your VBA code would be something like
Code: Select all
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByName("Sheet1")
For i = 0 to 4999 'row 1 to row 5000
oCell = oSheet.getCellByPosition(1,i)
StrLeng = LEN(oCell.String)
If StrLeng < 20 Then
oRow = oCell.getRows()
oRow.Height = 0.2 * 25.4 * 100 'measured in 1/100 mm
End if
next i
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macro For Row Height
I gave that a try, but seemed to get a syntax error where the "for" statement begins.
LibreOffice 4.2 on Linux
Re: Macro For Row Height
It runs for me. What exactly is the error?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macro For Row Height
I tried again and it did work. So, thank you very much for your time and effort.
LibreOffice 4.2 on Linux