This VBA seems to be "disobeyed" when executed. Any thoughts?

[TomCon]

I pressed the "Wrap Text" button in the UI and recorded VBA.

This is the VBA that was recorded:

Sub Macro5()
'
' Macro5 Macro
'

'
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

If i have a cell selected that will change the Row Height if the Wrap Text UI is pressed, it does the same if i execute this macro.

But then, i have the following macro (and i have tried a few variations of it). It was recorded with the exact same recording that should cause the Row Hight to be adjusted. But yet, as code, it is simply ignored. The code is executed (you can see it by stepping), but the Row Height is not changed. The result is as if that code block (With Selection...) was not there.

Any thoughts?

Is this an Excel bug? Is there something i can do to the code to change this behavior and get the .WrapText = False to be executed correctly in the VBA?

This is the Macro where the code is ignored:

Sub Comment_Out()
    Application.Calculation = xlCalculationManual
    
    Sheets("AL2019").Select
    Range("Main[[#Headers],[MainTyp]]").Select
    Selection.Offset(1, 0).Select
    Selection.Formula = "'" & Selection.Formula
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown

 

[6StringJazzer]

I cannot reproduce what you describe. I set this up by putting a long formula in a cell, which also has a long result. I turned on Text Wrap for the cell. Then I modified the code to refer to a cell I actually had (rather than setting up a table like yours). The code converted the formula to text, and then turned off Text Wrap. Then the row height changed to one line.

Can you give more detail about what is the content of the cell you are testing this with?

Also the macro recorder gives you a bunch of stuff you don't need. This won't solve your problem, but change this

    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

to this

    Selection.WrapText = False

 

[TomCon]

I cannot reproduce what you describe. I set this up by putting a long formula in a cell, which also has a long result. I turned on Text Wrap for the cell. Then I modified the code to refer to a cell I actually had (rather than setting up a table like yours). The code converted the formula to text, and then turned off Text Wrap. Then the row height changed to one line.

Can you give more detail about what is the content of the cell you are testing this with?

Also the macro recorder gives you a bunch of stuff you don't need. This won't solve your problem, but change this

    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

to this

    Selection.WrapText = False

Yes, i had tried reducing it once to that as well, but it did not solve the problem.

The cell has a very long formula in it that i used Alt-Ent in a bunch of times, so it increases the row height to greater than the screen size. it's an IFS formula with about ~20 conditions, and i put Alt-Ent after each condition so that it was more readable to me when i was maintaining it. Most of the conditions are less than one screen line so the Alt-Ent is generally after the comma, but a few occupy more than one screen line (in the formula bar), so a few of the lines have natural screen wrap. Thats about all i can think of to describe it.

Thanks if you have any further observations!

 

[6StringJazzer]

If you provide the actual formula I will test with your exact formula.

 

[TomCon]

If you provide the actual formula I will test with your exact formula.

Thanks, if it is not too much effort. As you see they are structured references for a table, so not sure what happens if you just paste this in, maybe you cannot instantiate it as a formula. This formula is in a column in a table. Another column in the table exhibits the exact same behavior, but that column has a bunch of LAMBDA functions so it is probably even worse to test on.

Let me know if you figure anything out. Thanks much!

=IFS(NOT(ISERROR(MATCH([@EvTyp],Ignore_List,0))),"NUL",
[@EvTyp]=3,"K",
OR([@EvTyp]=14,[@EvTyp]=15,[@EvTyp]=16),"W",
OR([@EvTyp]=9,[@EvTyp]=11),"PR",
[@EvTyp]=19,IFS(AND([@NmEr]=0,[@OutsPl]=1,OR([@[Bat-Dst]]=1,[@[Bat-Dst]]=2)),"FCN",AND([@NmEr]>0,[@OutsPl]>0),"EFCO",AND([@NmEr]=0,[@SacH]="T"),"FCSH",AND([@NmEr]>0,[@SacH]="T"),"FCSHE",[@NmEr]>0,IF(NOT(ISERROR(L_GetX([@Event]))),"EFCX","EFC"),[@OutsPl]=0,"FCS",[@OutsPl]=2,"FCDP",TRUE,IFERROR(L_GetHand([@Id2]),"xFC(hand)")),
[@NmEr]>0,IFS([@HitVa]>0,"EH",[@RBI]>0,"ERBI",[@OutsPl]>1,"EDP",[@SacH]="T","ESH",AND([@OutsPl]=1,[@EvTyp]=2),"EO",OR([@EvTyp]=18,[@EvTyp]=13,AND([@EvTyp]=2,[@OutsPl]=0)),"ES",TRUE,"xNumEr"),
[@SacH]="T","SH",
AND([@HitVa]>0,[@Clean]=TRUE),"HC",
AND([@HitVa]>0,[@NmEr]=0,[@OutsPl]>=1),"HOB",
AND([@HitVa]>0,[@NmEr]=0,[@Clean]<>TRUE),IFS([@Scored]=[@RBI],"HX",TRUE,"HXAJ"),
[@OFDP]=TRUE,IF([@FieA1]=[@FiePO1],"ODP","ODPh"),
[@TrP]="T","TP",
AND([@OutsPl]=2,[@DP]="F"),"NDP",
TRUE,"N")

 

[Alex Blakenburg]

Assuming it doesn't cause you other issues, have you tried using:

    With Selection.EntireRow