Run-Time error 1004 - Unable to set the RowHeight property of the range class

[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

[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.

[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.

[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.

[kb.net]

Hi,

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