Member Avatar for tgifgemini

Good morning,
I modified the code accordingly and got another "Run-Time error 1004 - Unable to set the RowHeight property of the range class" on the code below.

Also, please look at the spreadsheet attachment. Is there any way I can get rid of the spacing(gap) that separates the line of text in the "Comment" field? - gap between each line of text is too much and I want it to be closer. See spreadsheet attachment

Code that caused Runtime error:

.RowHeight = .RowHeight * Len(xlWksht.Range("A" & CStr(ii + 1)).text) / w

or

.RowHeight = .RowHeight * Len(xlWksht.Range("A" & ii + 1).text) / w

Below is my entire module:

For Each R In xlWksht.Range("A8:h8"): w = w + R.ColumnWidth: Next
    Do Until rsin.EOF = True
       ii = ii + 2
       xlWksht.Cells(ii, 1).Value = rsin![Req No]
       xlWksht.Cells(ii, 2).Value = rsin![Description]
       xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
       xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
       
       With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .WrapText = True
            .Orientation = 0
            .IndentLevel = 0
            .MergeCells = True
            .RowHeight = .RowHeight * Len(xlWksht.Range("A" & CStr(ii + 1)).text) / w
       End With
       
       rsin.MoveNext
    Loop

Thanks,
tgifgemini

xlsprdsht_with_spaces_between_text_in_comment_row.zip (52.03 KB)
  • 2 Contributors
  • 4 Replies
  • 2K Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by kb.net

Dani AI

Generated

A few gotchas here are causing the 1004. First, you are mixing units: RowHeight is in points, while ColumnWidth is roughly characters. Multiplying a row height by Len(...)/w (where w is a sum of column widths) will give nonsense values, sometimes 0 or >409.5, either of which can trigger the error. Second, because your target is a merged range, AutoFit does not work, and setting RowHeight on a merged row can be flaky unless you size it using a temporary, unmerged cell. Also be sure every Cells(...) and .Text read is qualified with xlWksht; .Text on a non-active sheet can return an empty string, producing a zero height. Good catch by on the 409.5 cap.

A reliable pattern is: unmerge, measure, then re-merge. This keeps the row height in points and avoids the AutoFit+merged-cell limitation.

' After writing the Comments cell value...
Dim rng As Range, txt As String
Dim totW As Double, oldW As Double, h As Double, c As Long

Set rng = xlWksht.Range(xlWksht.Cells(ii + 1, 1), xlWksht.Cells(ii + 1, 8))
txt = rng.Cells(1, 1).Value

For c = 1 To 8: totW = totW + xlWksht.Columns(c).ColumnWidth: Next

Application.ScreenUpdating = False
oldW = xlWksht.Cells(ii + 1, 1).ColumnWidth
rng.UnMerge
rng.WrapText = True
xlWksht.Cells(ii + 1, 1).ColumnWidth = totW
xlWksht.Cells(ii + 1, 1).Value = txt

xlWksht.Rows(ii + 1).AutoFit
h = xlWksht.Rows(ii + 1).RowHeight

xlWksht.Cells(ii + 1, 1).ColumnWidth = oldW
rng.Merge
rng.Cells(1, 1).Value = txt
xlWksht.Rows(ii + 1).RowHeight = WorksheetFunction.Min(409.5, WorksheetFunction.Max(1, h))
Application.ScreenUpdating = True

Two more quick tips:

  • Normalize the memo text before writing it: use xlApp.Clean (as found) and collapse double line breaks: repeatedly Replace(txt, vbLf & vbLf, vbLf).
  • Avoid merging if possible; a non-merged layout allows a simple Rows(ii + 1).AutoFit and is much more robust.

Recommended Answers

All 4 Replies

Member Avatar for kb.net

Hi,

For the extra spacing problem use the built in excel function clean which will remove all non printable chars.

As for the problem of row height it is becuase you are exceding the accepted row height which is 409.5 (546 pixels) so add an if statment so if the row height exceeds 409.5 then to keep it at 409.5 ex:

dim rowH as long
rowH = do the calculation of the new row height
if rowH > 409.5 then
me.range("a1").rowheight=409.5
else
me.range("a1").rowheight=rowH
end if

I hope this will work for you.

Member Avatar for tgifgemini

Hi kb.net.
Thanks for your input. As you know, I am using VB 6.0 platform and I tried the "clean" function and had a compile error:
this is my code:

xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & Trim(Clean(rsin![Comments]))

Any clues?
Thanks,
tgifgemini.

Member Avatar for tgifgemini

Hi kb.net.
I have been able to solve the spacing problem with this code:

xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & xlApp.Clean(Trim(rsin![Comments]))

However, the row height is still too high. You see, some records have less than two lines of text and some have very many(multiple) lines of text.
I want each row height to conform to the size of the data in the cell/row.

Thanks.
tgifgemini.

Member Avatar for kb.net

Hi,

You Have to adjust the column size because as mentioned you can't overcome the set row height.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.