VBA - Unwrap text column by searching for header name

[kparadise]

Hello,

I have to add a piece of code to the end of my current code. What I want to do is select the sheet named [consolidated], and search for the following three column headers; "Type1", "Type2", and "Type3"; and to unwrap the text on those entire columns.

 

[ParamRay]

Here is one way to do it:

Public Sub UnwrapColumns()
  Dim Headers(1 To 3) As String
  Dim Unwrap As Range
  Dim Cell As Range
  Dim Sh As Worksheet
  Dim i As Integer
  
  On Error Resume Next
  Set Sh = ThisWorkbook.Worksheets("Consolidated")
  
  On Error GoTo ErrorHandler
  If Sh Is Nothing Then
    MsgBox "The sheet 'Consolidated' does not exist in this workbook.", vbExclamation
    GoTo ExitHandler
  End If
  
  Headers(1) = "Type1"
  Headers(2) = "Type2"
  Headers(3) = "Type3"
  
  For i = LBound(Headers) To UBound(Headers)
    Set Cell = Sh.Cells.Find(Headers(i), _
      After:=Sh.Cells(Sh.Cells.CountLarge), _
      LookIn:=xlValues, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows)
    If Not Cell Is Nothing Then
      Set Unwrap = Intersect( _
        Range(Cell.Offset(1, 0), Sh.Cells(Sh.Rows.Count, Cell.Column)), _
        Sh.UsedRange)
      If Not Unwrap Is Nothing Then
        Unwrap.WrapText = False
        Set Unwrap = Nothing
      End If
      Set Cell = Nothing
    End If
  Next i
  
ExitHandler:
  Set Unwrap = Nothing
  Set Cell = Nothing
  Set Sh = Nothing
  Exit Sub
  
ErrorHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

 

[Fluff]

How about

Sub kparadise()
   Dim Ary As Variant
   Dim i As Long
   Dim Fnd As Range
   
   Ary = Array("Type1", "Type2", "Type3")
   With Sheets("Consolidated")
      For i = 0 To UBound(Ary)
         Set Fnd = Range("1:1").Find(Ary(i), , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then Fnd.EntireColumn.WrapText = False
         Set Fnd = Nothing
      Next i
   End With
End Sub

 

[kparadise]

Fluff, that code worked really well. I have a large code, so I needed to adjust "i" to "iWrap" in the entire code. But it did work. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback