Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit/wraptext on vbLf problem
Hello, I have a little formatting issue I am hoping one of you can help me with. I have a vbs script that is creating and populating a spreadsheet that will then be used to select some options and loaded into an API. One of the cells is a list that needs to appear as a list with each list item on it's own line within the cell. I use this code to populate the cell: 'create vblf delimited list strCyclist = Replace(WScript.Arguments.Item(6),",",vbLf) 'put it in the cell objSheet.Cells(intDBRow,9) = strCyclist I use this code to format: With objSheet .Columns("A:I").autofit .Columns("B:B").Columnwidth = 45 .Columns("A:J").wraptext = True End With For Each intRow In objsheet.usedrange intRow.EntireRow.AutoFit Next With my master plan here being that the autofit/wraptext would wrap that cell on the vbLf. But it turns out that the autofit/autowrap is wrapping on the first space in the text. My idea kinda works in that if you enlarge the column width larger than a single item in the list, then it wraps at the vbLf. So, I guess the wraptext is working as expected but the autofit is sizing the column off the first available space. Any Ideas on how to get this to work as desired are welcome. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit/wraptext on vbLf problem
Hi Ken,
You have basically answered your own question. "if you enlarge the column width larger than a single item in the list, then it wraps at the vbLf". Simply set the column width larger than what can be expected and then apply AutoFit. Even in the interactive mode you have to widen the column and then apply AutoFit or it adjusts to the widest existing data in the cells and tends to ignore linefeeds. Auto column width adjusts to the max width of the data in the cells as it exists prior to the AutoFit. A little constructive criticism on your other code. Why loop trough the used range to adjust the height. You can simply apply Autofit to the entire used range. objSheet.UsedRange.Rows.AutoFit 'or following With objSheet.UsedRange .Rows.AutoFit .Columns.AutoFit '(If required.) End With -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit/wraptext on vbLf problem
Hi OssieMac, Thanks for your response. I was afraid that was the answer. The problem is I don't know what the size is going to be. But it is what it is, I'm thinking I'll just write it to set the columnwidth based on the size of the string of one list item. As far as the row height code, yeah I know it's ugly... it's really obviously ugly if Excel is visible while it's doing it. It's that way because I could never find the right syntax to do it all at once, everything I tried (this was some time ago, as I'm reusing code) resulted in some error except the way that's shown. So! Thank you for the code, I'll use it! Thanks for your help, OssieMac. Ken "OssieMac" wrote: Hi Ken, You have basically answered your own question. "if you enlarge the column width larger than a single item in the list, then it wraps at the vbLf". Simply set the column width larger than what can be expected and then apply AutoFit. Even in the interactive mode you have to widen the column and then apply AutoFit or it adjusts to the widest existing data in the cells and tends to ignore linefeeds. Auto column width adjusts to the max width of the data in the cells as it exists prior to the AutoFit. A little constructive criticism on your other code. Why loop trough the used range to adjust the height. You can simply apply Autofit to the entire used range. objSheet.UsedRange.Rows.AutoFit 'or following With objSheet.UsedRange .Rows.AutoFit .Columns.AutoFit '(If required.) End With -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA inserting chr(10) or vbLf | Excel Discussion (Misc queries) | |||
autofit problem | Excel Programming | |||
Range WrapText and AutoFit with Bold | Excel Programming | |||
vblf and vbcr one last question | Excel Programming | |||
autofit row problem | Excel Discussion (Misc queries) |