proper case entire sheet except a few columns

[borgatarat]

I use:

Sub ConvertToProper() Dim ws As Object Dim LCell As Range 'Turn off screen updating to increase performance Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'Move through each sheet in your spreadsheet For Each ws In ActiveWorkbook.Sheets On Error Resume Next ws.Activate 'Convert all constants and text values to proper case For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues) LCell.Formula = StrConv(LCell.Formula, vbProperCase) Next Next ws 'Turn screen updating back on Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = TrueEnd Subfor proper case. How do I add to not apply proper case to certain rows? (B:C & F:I) Thank You!</pre>

 

[bruderbell]

Try this (your code but with an INTERSECT)

Sub ConvertToProper()
Dim ws As Object
Dim LCell As Range 'Turn off screen updating to increase performance
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'Move through each sheet in your spreadsheet
For Each ws In ActiveWorkbook.Sheets
    On Error Resume Next
    ws.Activate 'Convert all constants and text values to proper case
    For Each LCell In Intersect(Cells.SpecialCells(xlConstants, xlTextValues), _
        Range("B:C,F:I"))
    LCell.Formula = StrConv(LCell.Formula, vbProperCase)
    Next
    
Next ws 'Turn screen updating back on
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[bruderbell]

It was bugging me that the macro activated the worksheets, so I got rid of it. It seems to be a little cleaner:

Sub ConvertToProper()
Dim ws As Object
Dim LCell As Range 'Turn off screen updating to increase performance
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'Move through each sheet in your spreadsheet
For Each ws In ActiveWorkbook.Sheets
    On Error Resume Next
    For Each LCell In Intersect(ws.Cells.SpecialCells(xlConstants, xlTextValues), _
        ws.Range("B:C,F:I")) 'added ws in this statement to fully qualify which ranges are being called
    LCell.Formula = StrConv(LCell.Formula, vbProperCase)
    Next
Next ws 'Turn screen updating back on
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub