Hi there,
I have a problem that i need to solve fast, time is very short...
The problem is:
i have an input worksheet, with several rows formated as text, with row height set to autofit.
This text rows, i need to import in a collector worksheet, which gets data from lots of worksheets. The problem is that in the collector worksheet, i cannot set row height to autofit, and i have to find a way to extract the row height from the text input worksheet and use this height to format the collector worksheet rows...
The challenge is that i change frequently the text source( the collector is always the same) and i cannot edit links in VBA.
I am sure there is a simple solution to this, like a user defined function, and i need a start point...


Thanks in advance

Here's an example:

Sub RowHt()
Dim HT As Single
HT = Rows("12:12").RowHeight

MsgBox ("Row height is " & HT)

End Sub

thank you gjcase,
that code informs me about the row height, and i dont have the posibility to change the source worksheet . I need to use this information to set the row height in another worksheet
I tried instead this code, which works fine, but only when the source worksheet is OPEN, otherwise returns the #VALUE! error. How can i get rid of this error?
here is the code:

Option Explicit

Public Function RowHeight(ByVal rngHeight As Range) As Single

Dim rngRow As Range

For Each rngRow In rngHeight.Rows
RowHeight = RowHeight + rngRow.RowHeight
Next

End Function

=RowHeight('\\Flory\D\Sesiunea 4 - 141-iunie 2011\Dodan Danut\[text plan afaceri 141 - Dodan Danut.xls]text plan'!$A7))

Hi
May I suggest you use your current Macro to open the other worksheet and make the changes and then save and close it as part of your macro.

Hope this helps.

Tony(OTPM)

Thanks, Tony
The problem is that the source workbook name is always different, and this cannot be easily done in VBA (to change the links to another workbook).

Hi
In that case you can use an INPUT syntax within the VBA code to ask for the workbook name each time you run the code.
Hope this helps.
Tony(OTPM)

Catalin:

I'm confused. I assume you are reading data from other workbooks (vs worksheets, which would be contained within the same workbook, and therefore always accessible.) However, to read the data, you must have the source workbook open, and therefore you should be able to read the row height at the same time, correct?

I agree with Tony, a macro which opens each source, reads the data and rowheight, and writes that to the collector and then closes the source, would seem to be the way to go.

You are right, gjcase,
text data are in separate workbook.
But to read the data from that workbook, it is not necesary for that workbook to be opened.
Only the macro with User Defined Function looks like it is not working with the source workbook closed.
Excuse me for the confusion i created...
Looks like there is no other option but to open it so i can read the row height...
Thanks for advices, gjcase and Tony

Another solution might be to read the data into a workbook for which you can apply an autofit, and then read from this intermediate file the row height info into your collector workbook.

Dear friend, i do have a link between workbooks, but if i apply autofit to the cell containing the link to the source workbook, the height of the row will always be 15, no matter how long is the text.
I am working to a solution which counts characters in text : =LEN(A1), and to set the row height depending on this count. At first evaluation, looks like this method does not need the source workbook to be open...
Thanks again for your time gjcase, i will let you know if i find an acceptable solution, and please let me know if you have a better way

Catalin:

See the attached files. I have a source file, Book3.xlsm, an intermediate file, Intermediate.xlsm, which reads the data from the source, runs an autofit, and copies to the third file, collector.xlsm. See if this does what you are after.

Attached Files

	Book3.xlsm (9.0 KB, 14 views)
	Intermediate.xlsm (20.7 KB, 14 views)
	collector.xlsm (8.3 KB, 15 views)

Let there be problems, so we can find solutions...
in column B , i entered this formula: =LEN('C:\Users\Catalin\Desktop\Iordachi Gh. Gheorghe\[text plan afaceri 141 -Iordachi Gh. Gheorghe .xls]text plan'!$A7). The result is a number which i can use to set the row for 130 characters in a row, with a formula , in column C: =CEILING(15*D5/130;15) The result is a multiple of 15, 30, 45, 60, depending on text length.
And finally, the macro works great...

Sub Height()
Application.ScreenUpdating = False
Rows("565:565").RowHeight = Sheets("Rand").Range("C22")
Rows("566:566").RowHeight = Sheets("Rand").Range("C23")
Rows("595:595").RowHeight = Sheets("Rand").Range("C25")
Rows("597:597").RowHeight = Sheets("Rand").Range("C26")
Rows("599:599").RowHeight = Sheets("Rand").Range("C27")
Rows("600:600").RowHeight = Sheets("Rand").Range("C28")
Rows("4:4").RowHeight = Sheets("Rand").Range("H5")
Rows("65:65").RowHeight = Sheets("Rand").Range("H6")
Rows("214:214").RowHeight = Sheets("Rand").Range("H7")
Rows("215:215").RowHeight = Sheets("Rand").Range("H8")

Application.ScreenUpdating = True
End Sub
Many thanks to gjcase and Tony, for guidance through this process, you're great guys!