We help IT Professionals succeed at work.

Delete Entire Column by column name - using VBA

Hello,
I need to delete Entire column in Excel sheet using column name.
In my example I need to delete the 3rd column with the name "DateOfBirth", instead of using column number.
delete column by its nameThe code I'm using right now is:
Sub sbVBS_To_Delete_EntireColumn ()
    Columns(1).EntireColumn.Delete
End Sub

Open in new window

Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
try this:

Sub sbVBS_To_Delete_EntireColumn(columnName As String)
    Columns(columnName).EntireColumn.Delete
End Sub

Open in new window


to implement:

call sbVBS_To_Delete_EntireColumn("C")

Open in new window

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try something like this...

Change the column header on the first line of code as per your requirement.

Const headerName As String = "dateofbirth"

Sub DeleteColumn()
Dim ws      As Worksheet
Dim hdrCel  As Range

Application.ScreenUpdating = False

Set ws = ActiveSheet

Set hdrCel = ws.Rows(1).Find(what:=headerName, lookat:=xlWhole, MatchCase:=False)

If Not hdrCel Is Nothing Then
    hdrCel.EntireColumn.Delete
Else
    MsgBox "The column with header " & headerName & " was not found!", vbExclamation
End If
Application.ScreenUpdating = True
End Sub

Open in new window

Mohammad Alsolaimanapplication programmer

Author

Commented:
Thanks Ryan
That's much better than column number, but I would prefer if there is any way to delete it using the name appear in the first row, which in my case is " DateOfBirth" . is it possible to do so?
Mohammad Alsolaimanapplication programmer

Author

Commented:
thanks to all of you
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!