Report

Need to delete columns based on Column header [Solved]

Ask a question yesare - Latest answer on Sep 8, 2016 04:44PM
Hello,
I need to have an macro which will delete the columns from my excel based on the Column header.
Exsample:
I have the columns,
Name | Address1 | Address2 | Country | Zipcode |
I want to have the sheed with only
Name | Country | Zipcode
the macro should delete the other columns based on a search on column header, i.e
if Address1 is there it needs to be deleted.
advance thanks for your assistance.
See more 
Helpful
+6
moins plus
Hi Yesare,

Try this code:
Sub test()
    Set MR = Range("A1:E1")
For Each cell In MR
    If cell.Value = "Address1" Or cell.Value = "Address2" Then cell.EntireColumn.Delete
        Next
End Sub

Please note that if column B is deleted column C will be removed to the place of column B and will not be checked again. This means you might have to run the code multiple times.

Best regards,
Trowa
Missi- May 13, 2015 08:27AM
This is NOT working If am trying to delete multiple columns
Reply
Add comment
Helpful
+6
moins plus
If you want to use this for other workbooks as well, you will have to define more ranges like:

Sub test()
    Set MR = Range("A1:E1")
    Set MRB1 = Workbooks("Book1").Worksheets("Sheet1").Range("A1:E1")
For Each cell In MR
    If cell.Value = "Address1" Or cell.Value = "Address2" Then cell.EntireColumn.Delete
        Next
For Each cell In MRB1
    If cell.Value = "Address1" Or cell.Value = "Address2" Then cell.EntireColumn.Delete
        Next
End Sub

Note that "cell", "MR" (My Range) and "MRB1" (My Range Book 1) can be changed into anything to make the code more understandable/readable for you.

Best regards,
Trowa
Add comment
Helpful
+3
moins plus
thanks its working.. can you give me a idea how we can achieve this for multiple workbook
Add comment
Helpful
+2
moins plus
Is there any way to updated the code so the deletion will loop until it is done??
Add comment
Helpful
+0
moins plus
Thanks. This is working, but i need the header to remain
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!