Report

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

Ask a question yesare - Last answered on Apr 21, 2017 at 02:31 PM by 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.


See more 
Helpful
+9
plus moins
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
Was this answer helpful?  
Helpful
+4
plus moins
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 at 08:27 AM
This is NOT working If am trying to delete multiple columns
Helpful
+4
plus moins
Is there any way to updated the code so the deletion will loop until it is done??
Helpful
+3
plus moins
thanks its working.. can you give me a idea how we can achieve this for multiple workbook
Helpful
+0
plus moins
Thanks. This is working, but i need the header to remain
topazfae- Apr 21, 2017 at 02:31 PM
I just want to them to loop. like Question Id * anything after ID should not matter - just delete based on Question ID *
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!