none
Delete columns whose header is not equal to a certain value RRS feed

  • Question

  • Hi all,

    I'm trying to delete a column if its header (i.e. the value in the top row) is NOT the same as the worksheet name. So, for example, if the worksheet name is "TEST 3", the columns in that worksheet that do not have the value "TEST 3" in Row 1 are to be deleted. In the screen shot below, I want to delete all the columns except  C and H from this worksheet "TEST 3", because their values are not "TEST 3". Basically, Worksheet "TEST 1" is to contain only the columns with the header "TEST 1", Worksheet "TEST 2", only "TEST 2", and so on.

    Below is my try at VBA, but obviously i'm here because it doesnt work. As you can tell, i'm not very familiar with VBA. Hope someone could help... 

    Thank you!

    Jay

                    

    Sub Delete_Columns()

        For x = 2 To Sheets.Count

            For y = 2 To ActiveSheet.UsedRange.Columns.Count

                If Sheets(x).Name <> Worksheets(y).Cells(1, y).Value Then
                Sheets(x).Columns(y).Delete
                End If
            Next

        Next

    End Sub


    • Edited by jay.nz Wednesday, October 3, 2018 7:04 AM
    Wednesday, October 3, 2018 7:04 AM

Answers

  • I dont know if this is the best, but i think i have sussed it by tweaking your code a little!

    Sub DeleteSpecificColumn_3()
    Dim x As Long
    Dim y As Long
    Dim z As Long

        y = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column 'This doesnt seem ideal but all the worksheets in this workbook are copies of Worksheet(1), so I decided to use the total number of columns of Worksheet(1).
            
        For z = 2 To ThisWorkbook.Worksheets.Count 'I want to keep Worksheet(1) as master, but apply below condition to all the other worksheets, hence this code
            For x = y To 2 Step -1 'Instead of counting up, I'm counting down so the column positions that this loop will go through do not move.
                If Worksheets(z).Cells(1, x).Value <> Worksheets(z).Name Then Worksheets(z).Cells(1, x).EntireColumn.Delete
            Next x
        Next z
    End Sub

    After a lot of trial and error, above worked! Thank you very much Lina for your help! If you have any other suggestions that could make the above code better, please do let me know! :-)

    Thanks

    Jay


    • Edited by jay.nz Friday, October 5, 2018 8:04 AM
    • Marked as answer by jay.nz Friday, October 5, 2018 8:29 AM
    Friday, October 5, 2018 8:04 AM

All replies

  • Hi jay.nz,

    Please use the following code:

    Sub DeleteSpecifcColumn()
        Set MR = Range("B1:J1")
        For Each cell In MR
            If cell.Value <> "TEST3" Then cell.EntireColumn.Delete
        Next
    End Sub

    For more information, please refer to the following link:

    Delete entire columns based on header value with VBA code

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    Wednesday, October 3, 2018 7:15 AM
  • Oh thanks Lina for such a quick reply! Also thanks for the link. 

    Just a couple of questions as i'm trying to learn as well, 

    1. I see you set Range("B1:J1"), but last column in my workbook can be anything. I mean, it could be K, Z, or AA, and so on. How do I set this range to be dynamic?

    2. If I want to apply this macro to across all worksheets in this workbook (i.e. not just "TEST 3"), what do i do? 

    Wednesday, October 3, 2018 7:27 AM
  • Hi jay.nz,

    Sorry for the late reply. 

    >>1. I see you set Range("B1:J1"), but last column in my workbook can be anything. I mean, it could be K, Z, or AA, and so on. How do I set this range to be dynamic?

    lColumn = MySheet.Cells(1, Columns.Count).End(xlToLeft).Column

    >>2. If I want to apply this macro to across all worksheets in this workbook (i.e. not just "TEST 3"), what do i do? 

    For Each MySheet In ThisWorkbook.Worksheets

    Here is the final code, please try the following code:

    Option Explicit
    Sub DeleteSpecifcColumn()
    Dim i As Long
    Dim M As String
    Dim lColumn As Long
    Dim MySheet As Worksheet
    
        For Each MySheet In ThisWorkbook.Worksheets
        'Find Last Column
        lColumn = MySheet.Cells(1, Columns.Count).End(xlToLeft).Column
        
        M = MySheet.Name
        For i = 2 To lColumn
            If Cells(1, i).Value <> M Then Cells(1, i).EntireColumn.Delete
        Next i
        Next MySheet
    End Sub

    Hopefully it helps you

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.





    • Edited by Lina-MSFT Thursday, October 4, 2018 5:50 AM
    Thursday, October 4, 2018 5:07 AM
  • Thank you very much Lina.

    Unfortunately, it's not returning the result i want. I think i might know why but have no idea how to go about it.

    With <For i = 2 to lColumn ... Next i>, it goes to column 2, 3, 4 and so on, right? But when column 2 is deleted, column 3 becomes column 2 (i.e. column 3 now moves left to the position of column 2), column 4 becomes column 3 and so on. But because i=2 has gone through once already, the if-then code skips the new column 2 (which was column 3) over to the new column 3. Arrrrrrg... it's really hard to explain in writing. 

    What i'm thinking is that, instead of the variance i moving up to the next number, it should start again from 2 until it reaches the value of lColumn.

    Ah this is a lot harder than i thought it would be... :-(

    Thursday, October 4, 2018 8:34 AM
  • Hi jay,

    Sorry for the late reply. 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.

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    Friday, October 5, 2018 5:33 AM
  • I dont know if this is the best, but i think i have sussed it by tweaking your code a little!

    Sub DeleteSpecificColumn_3()
    Dim x As Long
    Dim y As Long
    Dim z As Long

        y = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column 'This doesnt seem ideal but all the worksheets in this workbook are copies of Worksheet(1), so I decided to use the total number of columns of Worksheet(1).
            
        For z = 2 To ThisWorkbook.Worksheets.Count 'I want to keep Worksheet(1) as master, but apply below condition to all the other worksheets, hence this code
            For x = y To 2 Step -1 'Instead of counting up, I'm counting down so the column positions that this loop will go through do not move.
                If Worksheets(z).Cells(1, x).Value <> Worksheets(z).Name Then Worksheets(z).Cells(1, x).EntireColumn.Delete
            Next x
        Next z
    End Sub

    After a lot of trial and error, above worked! Thank you very much Lina for your help! If you have any other suggestions that could make the above code better, please do let me know! :-)

    Thanks

    Jay


    • Edited by jay.nz Friday, October 5, 2018 8:04 AM
    • Marked as answer by jay.nz Friday, October 5, 2018 8:29 AM
    Friday, October 5, 2018 8:04 AM
  • Hi jay.nz,

    Thanks for your asking. Please remember to mark the replies(Include your solution) as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    Friday, October 5, 2018 8:10 AM