Need to delete columns based on Column headerSolved/Closed

Question

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.


Configuration: Windows XP / Internet Explorer 6.0

TrowaD · Accepted Answer

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

TrowaD · Answer

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

yesare · Answer

thanks its working.. can you give me a idea how we can achieve this for multiple workbook

amroueh · Answer

Is there any way to updated the code so the deletion will loop until it is done??

Krish · Answer

Thanks. This is working, but i need the header to remain

Need to delete columns based on Column header

5 responses

Similar discussions

Subscribe To Our Newsletter!