Clear Columns based on Header Value while keeping said header

[amr411]

Hi everyone!

First of all, apologies if this has been asked before. I Googled and searched within the forum but was unable to find the answer for this.

I'm trying to create a macro that clears all column contents based on the header. I had been using the following code:

Attribute VB_Name = "Module1"Sub VBA_Clear_Contents_Range()
Range("A2:A1000,L2:L10000,DL2:DL10000").ClearContents
End Sub

Which was working just fine, until I realized the columns change place with every export. What I am trying to do is to create a Macro that finds the following values:

Campaign ID
Ad Set ID
Ad ID

And clears all values under the header. Everything I tried so far deletes the whole column, including the header. I am almost sure it has to do with the "offset" function but I am not sure I know how to use it right.

I've been trying with this code, but I am also unsure as to how to search for multiple values.

Sub Find_and_Delete_Col()

Dim strFindThis As String
Dim Found As Range


strFindThis = "Campaign ID"


Set Found = Range("1:1").Find(What:=strFindThis, Lookat:=xlPart, MatchCase:=False)


If Not Found Is Nothing Then
Found.Offset(0, 1).EntireColumn.Clear
Else
MsgBox "No match found for: " & strFindThis, vbInformation, "No Match"
End If


End Sub

Any help would be greatly appreciated!

 

[Fluff]

Hi & welcome to MrExcel
Untested, but try

Sub Find_and_Delete_Col()

Dim Fnd As Range
Dim Ary As Variant
Dim Usdrws As Long
Dim i As Long

Ary = Array("Campaign ID", "Ad Set ID", "Ad ID")
Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 0 To UBound(Ary)
   Set Fnd = Range("1:1").Find(What:=Ary(i), Lookat:=xlPart, MatchCase:=False)
   If Not Fnd Is Nothing Then
      Fnd.Offset(1).Resize(Usdrws - 1).Clear
   Else
      MsgBox "No match found for: " & Ary(i), vbInformation, "No Match"
   End If
Next i

End Sub

 

[amr411]

Hi & welcome to MrExcel
Untested, but try

Sub Find_and_Delete_Col()

Dim Fnd As Range
Dim Ary As Variant
Dim Usdrws As Long
Dim i As Long

Ary = Array("Campaign ID", "Ad Set ID", "Ad ID")
Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 0 To UBound(Ary)
   Set Fnd = Range("1:1").Find(What:=Ary(i), Lookat:=xlPart, MatchCase:=False)
   If Not Fnd Is Nothing Then
      Fnd.Offset(1).Resize(Usdrws - 1).Clear
   Else
      MsgBox "No match found for: " & Ary(i), vbInformation, "No Match"
   End If
Next i

End Sub

That worked perfectly! Tested on various files and worked every time.

Thanks a lot!

 

[Fluff]

Glad to help & thanks for the feedback