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

Report
-
 topazfae -
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.


5 replies

Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
444
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
9
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
444
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
Is there any way to updated the code so the deletion will loop until it is done??
thanks its working.. can you give me a idea how we can achieve this for multiple workbook
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 *