How to check if column header exists and if not then add column with that header
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to set all non zero column cells to column header |
| How to count cells in a dynamic range matching two criteria given in table headers |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.