Macro to find and delete column based on cell value

Question

I have two sheets in an Excel workbook. I would like a VBA code to search for the content of cell J19 in Sheet2 and delete the column with the matching cell in Sheet1. I have been searching all around Google to look for some VBA codes, but I haven't found anything yet that works.

Does anybody here know how to do this? I have zero experience in VBA.

Welcome to S.O! Have you tried anything? If so, please, provide the code, take a look to the tour and how to ask. Friendly reminder: StackOverflow is not a "we code for you" service provider. Introduction to VBA Hint: You are never going to find the solution for your specific problem, break it in little pieces, first "Find a word excel vba" then tailor it to your needs. — Sgdva

Anastasiya-Romanova 秀 Anastasiya-Romanova 秀 · Accepted Answer · 2016-08-19T22:28:31

You could try this code to perform such a task.

Sub FindMatchAndThenDeleteColumn()

    FindContent = Worksheets("Sheet2").Range("J19")

    For Each Cell In Worksheets("Sheet1").UsedRange.Cells
        If Cell.Value = FindContent Then Columns(Cell.Column).Delete
    Next

End Sub

Put the code above in your workbook's module. Note that FindContent and Cell are randomly chosen here, you can use any names. I use

   For Each Cell In Worksheets("Sheet1").UsedRange.Cells

to loop through every cell that is in use in Sheet1. It will check everything in the range from cell A1 to the last cell with data (the bottom right-most cell). If the content of cell J19 is a text, you can declare the variable FindContent as a String type, i.e. Dim FindContent As String. If it's a number, you can declare it as a Long type or a Single type or any number type that fits the content. Here I don't declare it which means it defaulting to a Variant type. And since you're a beginner in VBA, you may learn it from Excel Easy. Hope this helps.

Macro to find and delete column based on cell value

1 Answers