How to check if column header exists and if not then add column with that header

How to check to check if column header exists and if not then add column with that header

Requirement:

The user wants to write a macro which would check if a data sheet has a set of columns and if not then add a column with that header.

For example, the user wants columns Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, col9 to be present and in the same order but the data sheet has missing columns in between so the user wants the missing column to be created there with the header.

Solution:

Code:

Option Explicit

Sub CheckColumnHeadings()

CheckColumnHeadingIsPresent "Col1", 1
CheckColumnHeadingIsPresent "Col2", 2
CheckColumnHeadingIsPresent "Col3", 3
CheckColumnHeadingIsPresent "Col4", 4
CheckColumnHeadingIsPresent "Col5", 5
CheckColumnHeadingIsPresent "Col6", 6
CheckColumnHeadingIsPresent "Col7", 7
CheckColumnHeadingIsPresent "Col8", 8
CheckColumnHeadingIsPresent "Col9", 9
CheckColumnHeadingIsPresent "Col10", 10

MsgBox "Column headings checked.", _
       vbOKOnly + vbInformation, _
       "Column Headings Checked"

End Sub

Sub CheckColumnHeadingIsPresent(strColumnHeading As String, intPosition As Integer)

If wsData.Cells(1, intPosition) <> strColumnHeading Then
   ' The column heading is missing so insert it at intPosition.
   wsData.Columns(intPosition).Select
   Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   wsData.Cells(1, intPosition) = strColumnHeading
End If

End Sub

Note: The code assumes that the column headings are in row 1.

Obtained from the OzGrid Help Forum.

Solution provided by Tom Rowe.