I am using Excel for Microsoft 365.
On the first worksheet, I have recorded a macro to adjust column widths and row heights. I then switched to the second worksheet and ran this macro and realized I have the problem described below.
Each worksheet contains at least one (maybe multiple) columns whose column name in the header row (row 1) contains the string "foo". The locations of these columns vary from worksheet to worksheet.
There are some steps I perform on all columns. These steps work fine. However, there's an extra step I'd like to perform on the "foo" column(s). Specifically, I'd like to change the width of those columns to 30. Since the locations of these columns vary from worksheet to worksheet, this isn't so straightforward to do.
Here is VBA code that illustrates the problem:
Sub Macro1()
' This part works.
Application.Goto Reference:="R1C1"
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ColumnWidth = 100
Cells.Select
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit
' The problem starts here.
' How may I do this for all columns whose column name in the header row contains "foo"?
' There are an arbitrary number of such columns and their locations vary from worksheet to worksheet.
Columns("G:G").Select
Selection.ColumnWidth = 30
Columns("L:L").Select
Selection.ColumnWidth = 30
' The rest of this macro works fine.
Cells.Select
Cells.EntireRow.AutoFit
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End Sub
In this code, I set columns G and L to have width 30. Rather than hardcode the columns, how may I change this code to set the width to 30 of any column whose column name (in the header row) contains "foo"?