Delete entire column if a specific string is found in a row.

[Tony_Caliente]

Thank you for reading.

Along row 14 (from A14 to M14), I am looking for the following values: Name, Date, Currency, Amount. If any of the strings appear in any of the cells along row 14, I want to delete the entire column storing that value. So basically, search row 14 for any of all of these words (Name, Date, Currency, Amount), and delete the column storing the word. So if Name appears in A2 delete column 2, If Currency appears in column A3, delete column 3. I never know which column the string will appear. Thank you.

[DocAElstein]

Good Morning, Tony from Atlanta,
I am slightly confused by your desription... , you say Along row 14 (from A14 to M14) ... then say .. if Name appears in A2 delete column 2, If Currency appears in column A3, delete column 3
My guess is maybe you meant .... if Name appears in B14 delete column 2, If Currency appears in column C14, delete column 3


I think something like this can probably be done in lots of different ways..

This is the first way that came to mind…

I take each string, use the Range.Find Method https://docs.microsoft.com/en-us/office/vba/api/excel.range.find with that string as the searched for thing, and, if Range.Find finds it, it returns a range object ( cell ) which is that cell where the string is found, Then I delete the entire column of that found range object

A code example is below.
I tested it on this:
Using Excel 2007 32 bit
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N

13


14AmountNameDateCurrency


15
Worksheet: Tabelle1

After running the code, I get this:
Using Excel 2007 32 bit
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N

13


14


15


16


17
Worksheet: Tabelle1


Let me know if you need more help or any more explanation to the code

Alan
from Hof
in Bavaria
Germany

_.___________

Code Example:



Option Explicit
Sub DeleteColumnFromRange_Find() ' http://www.excelfox.com/forum/showthread.php/2288-Delete-entire-column-if-a-specific-string-is-found-in-a-row
Rem 1 Worksheet Info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim rngA14M14 As Range: Set rngA14M14 = Ws1.Range("A14:M14")
Rem 2 Strings to search for. For convenience, I put them in an array
Dim arrStrgs(1 To 4) As String: Let arrStrgs(1) = "Name": arrStrgs(2) = "Date": arrStrgs(3) = "Currency": arrStrgs(4) = "Amount"
Rem 3 Go through each string and delete the entire column of the range object cell which it is found in
Dim SteerThing As Variant ' the steering thing must be a variant or object Type
Dim rngFand As Range 'For any found cell containing the string at any time searched for
For Each SteerThing In arrStrgs() ' the steering thing must be a variant or object Type. I think it looks for items or elements in the In thing if you don't specify an object. The variant type will hold the string, and the Ramge.Find will "coerce" the given thing to as string if it can, so no problems with type mismatch occur
Set rngFand = rngA14M14.Find(What:=SteerThing, After:=rngA14M14.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not rngFand Is Nothing Then ' range.Find returns Nothing if it does not find what it searches for
rngFand.EntireColumn.Delete shift:=xlToLeft
' Set rngA14M14 = rngA14M14.Resize(1, rngA14M14.Columns.Count - 1) ' This does not seem necerssary - the range appears to be automatically reduced by 1 column after a column is deleted
Else '
End If
Next SteerThing
End Sub

The code is also in the attatched file
( I put it in a Worksheet code module. To see that code , then right click on the first tab, and select something like "Show Code"
: Right Click Tab Show Code.JPG : https://imgur.com/7Vjl6ts )
2110
( If you already have the VB Editor window open ( for example by Alt+F11 ) then double click on Tabelle1 in the VBA Project Window)