Macro to delete columns based on header

Can I have a macro delete columns in a spreadsheet that I want to discard based on the column heading?
I want the macro to delete the same headers each time it is run. The content is static: Example I want to delete columns with these headings. These should always be in the same position on the spreadsheet.

I get spreadsheets from our IT department that have about 30-75 headings, most of which I want to automatically delete for the reports I need.

Example:
A1 ID
B1 RECEIPT_NO
C1 FIRST_NAME
D1 LAST_NAME
E1 SPOUSE_FIRST_NAME DELETE THIS COLUMN
F1 SPOUSE_LAST_NAME DELETE THIS COLUMN
G1 DATE_CREDITED
H1 CREDIT_AMOUNT
I1 FIRST_ENTRY
J1 PRIMARY_STAFF
K1 SECONDARY_STAFF

Please try this.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This code will delete the columns without popups displayed. In any deletion i.e., row deletion or column deletion, a popup will appear with message of deletion and you need to click OK on it.
______________________________________________________________________________

if you feel this has helped you, please add to reputation

Shyamhappy,

Not

Please Login or Register  to view this content.

But should be

Please Login or Register  to view this content.

Sorry about that it was my typo error.

Hello shyamhappy,

I renamed the macro to newdeleteheader2 and changed the range to Columns G:H. By the way, I am using Excel 2010 not 2007.

I got a run-time error "1004"
Select method of Range class failed.



Sub newdeleteheader2()

Dim Sht1 As Worksheet
Set Sht1 = ThisWorkbook.ActiveSheet

Application.DisplayAlerts = False

With Sht1
.Columns("G:H").Select
Selection.Delete Shift:=xlToLeft
End With

Set Sht1 = Nothing
End Sub

Please Login or Register  to view this content.

Hi lutuxel,

This worked....

I edited it to delete a series of columns. The macro deletes the higher columns first, so that the column address isn't confused.

Sub jw_test1()
Dim wb As Workbook
Dim ws As Worksheet
Set ws = ActiveSheet

With ws
.Columns("AD:AE").Delete
.Columns("V:AA").Delete
.Columns("R:S").Delete
.Columns("O").Delete
.Columns("G:H").Delete
.Columns("E").Delete
.Columns("D").Delete
End With
Set wb = Nothing
Set ws = Nothing
End Sub

sfadam,
Please use code tags with your code as per forum's rule.

Please Login or Register  to view this content.

maybe as an option

Please Login or Register  to view this content.

Macro to Compare two sheets header and delete columns where the header doesn't match