Need to delete columns based on Column header [Solved/Closed]

- - Latest reply:  topazfae - Apr 21, 2017 at 02:31 PM
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 

5 replies

Best answer
Posts
2448
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
February 14, 2019
409
9
Thank you
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

Say "Thank you" 9

A few words of thanks would be greatly appreciated. Add comment

CCM 4353 users have said thank you to us this month

Posts
2448
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
February 14, 2019
409
4
Thank you
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
This is NOT working If am trying to delete multiple columns
4
Thank you
Is there any way to updated the code so the deletion will loop until it is done??
3
Thank you
thanks its working.. can you give me a idea how we can achieve this for multiple workbook
0
Thank you
Thanks. This is working, but i need the header to remain
I just want to them to loop. like Question Id * anything after ID should not matter - just delete based on Question ID *