To check if column header exists and if not then add column with that header

aroy1195 · Jun 6th 2019

I want 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.

Say, I want 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 I want the missing column to be created there with the header.

Tom Rowe · Jun 6th 2019

Hi,

Try something along the following lines...

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

Display More

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

I have attached an example workbook 'Check and Insert Missing Columns.xlsm' with the above code.

Regards,

Tom Rowe...

aroy1195 · Jun 6th 2019

Thank you for your help [USER="328923"]Tom Rowe[/USER]. It worked.

To check if column header exists and if not then add column with that header

Participate now!

Share

Users Viewing This Thread