CODEWITHSUNDEEP
vbaexcelmacros
AlbaTroels
AlbaTroels

Reputation: 290

Macro to find and delete column based on cell value

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.

Upvotes: 0

Views: 2510

Answers (1)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

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.

Upvotes: 1

Related Questions