Delete column which starts with a value

[ExcelMaker007]

How to Delete column which column header starts with a value

 

[Fluff]

xlPart really should be xlWhole otherwise the code would change a heading like "Awaken" to "ATRUE", even though it would not delete the column.

Both codes would be more robust with something, say, On Error Resume Next in case there were no headers with "Wake*" (eg the code accidentally gets run a second time)

Two very good points Peter.

 

[JLGWhiz]

xlPart really should be xlWhole otherwise the code would change a heading like "Awaken" to "ATRUE", even though it would not delete the column.

Also needs xlWhole specified in case another Find has previously been done with xlPart in which case a similar issue to the above would arise.
This code would of course also incorrectly delete any column that happened to be missing a heading in row 3.

Both codes would be more robust with something, say, On Error Resume Next in case there were no headers with "Wake*" (eg the code accidentally gets run a second time)

I was under the impression that using xlWhole would cause it to ignore anything that exceeded "Wake", but maybe not "Wake*". Haven't tried that yet, but will. tx Peter.

 

[Peter_SSs]

but maybe not "Wake*"

Correct. "Wake*" with xlWhole will apply to anything that starts with "Wake", which is what the OP wanted.

 

[JLGWhiz]

something else to be considered in future use. Since the Range.Replace method uses the Find function as part of its execution, setting the LookAt,, SearchOder and MatchCase parameters in either the Find function or the Replace statement will determine the default setting for the next use of either the Find or the Replace. MS recommentds specifying these parameters on each use to avoid inadvertant results. I was aware of this on the Find statement, but did not realize the the two were linked within the vba application software. Learn something every day.?

 

[Peter_SSs]

LookAt,, SearchOder and MatchCase ... MS recommentds specifying these parameters on each use to avoid inadvertant results.

Exactly, that's why I mentioned it

Also needs xlWhole specified in case another Find has previously been done with xlPart in which case a similar issue to the above would arise.

I din't mention MatchCase since the OP hasn't specified whether the match has to be case-specific or not & I din't mention SearchOrder since that is irrelevant to this question.

 

[JLGWhiz]

I think the OP left town.

 

[ExcelMaker007]

xlPart really should be xlWhole otherwise the code would change a heading like "Awaken" to "ATRUE", even though it would not delete the column.

Also needs xlWhole specified in case another Find has previously been done with xlPart in which case a similar issue to the above would arise.
This code would of course also incorrectly delete any column that happened to be missing a heading in row 3.

Both codes would be more robust with something, say, On Error Resume Next in case there were no headers with "Wake*" (eg the code accidentally gets run a second time)

How to handle that issue... Can we have try catch to it

 

[Peter_SSs]

How to handle that issue... Can we have try catch to it

Which issue?

If you mean the "Awaken" example I gave and you want a vba approach, try Joe4's code or Fluff or JLGWhiz codes with xlPart changed to xlWhole.

If you meant the issue of no "Wake*' headers then again try Joe4's code or add the blue lines below (as well as the red change) to Fluff's code or similar to JLGWhiz' code.

Sub ExcelMaker()
   With Range("3:3")
      .Replace "Wake*", True, xlWhole, , False, , False, False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireColumn.Delete
      On Error GoTo 0
   End With
End Sub