vba code exact match string

spsquirrel

New Member
Joined
Aug 2, 2019
Messages
3
Below is the beginning of my code, the only issue I'm having is that the below list when deleting should only delete on an exact match.
So for example if I have 'carrier name' as a column header and 'carrier name 2' as a column header by the list below only carrier name column should be deleted and carrier name 2 should remain.
Currently the code is deleting both columns.
how do I make this exact match?


Code:
Dim rngFound As Range
Dim rngDel As Range
Dim arrColumnNames() As Variant
Dim varName As Variant
Dim strFirst As String


Code:
'list of column headers to delete
    arrColumnNames = Array("Anticipated Billing Date Year/Period", "Carrier Name", "Customer Classification", _
    "Customer Classification Description", "Customer Group", "Customer Hierarchy 1", "Customer Hierarchy 2", _
    "Customer Material", "Customer PO Date", "Document Currency", "Fixed Conversion Cost at Standard (USD)", _
    "Freight Expense (USD)", "Freight on Order", "Global Region", "Internal Material Description", _
    "Material Cost at Standard (USD)", "Net Order Value", "Order Commission Value (USD)", "Order Gross Margin Value (USD)", _
    "Order Reason", "Order Reason Description", "Order Rebate Value (USD)", "Payer", "Payer Country", "Payer Name", "Payment Term", _
    "PPV (USD)", "Product Base", "Product Base Desc.", "Product Class", "Product Class Desc.", "Product Group", "Product Sub-Group", _
    "Product Sub-Group Desc.", "Profit Center", "Purchase Order Document", "Reval (USD)", "Sales Group", "Sales Office", _
    "Scheduled Ship Week", "Ship to", "Ship to Postal Code", "Ship to Region", "Shipping Condition", "Sold to City", "Sold to Country", _
    "Sold to Region", "Variable Conversion Cost at Standard (USD)", "VAT Tax Code", "VAT Value (USD)")
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,098
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
It would help if you posted the code that is deleting the headers.
 

spsquirrel

New Member
Joined
Aug 2, 2019
Messages
3
Hi & welcome to MrExcel.
It would help if you posted the code that is deleting the headers.

Code:
Sub remove_columns()
    Dim rngFound As Range
    Dim rngDel As Range
    Dim arrColumnNames() As Variant
    Dim varName As Variant
    Dim strFirst As String
'list of column headers to delete
    arrColumnNames = Array("Anticipated Billing Date Year/Period", "Carrier Name", "Customer Classification", _
    "Customer Classification Description", "Customer Group", "Customer Hierarchy 1", "Customer Hierarchy 2", _
    "Customer Material", "Customer PO Date", "Document Currency", "Fixed Conversion Cost at Standard (USD)", _
    "Freight Expense (USD)", "Freight on Order", "Global Region", "Internal Material Description", _
    "Material Cost at Standard (USD)", "Net Order Value", "Order Commission Value (USD)", "Order Gross Margin Value (USD)", _
    "Order Reason", "Order Reason Description", "Order Rebate Value (USD)", "Payer", "Payer Country", "Payer Name", "Payment Term", _
    "PPV (USD)", "Product Base", "Product Base Desc.", "Product Class", "Product Class Desc.", "Product Group", "Product Sub-Group", _
    "Product Sub-Group Desc.", "Profit Center", "Purchase Order Document", "Reval (USD)", "Sales Group", "Sales Office", _
    "Scheduled Ship Week", "Ship to", "Ship to Postal Code", "Ship to Region", "Shipping Condition", "Sold to City", "Sold to Country", _
    "Sold to Region", "Variable Conversion Cost at Standard (USD)", "VAT Tax Code", "VAT Value (USD)")
    
    
    For Each varName In arrColumnNames
        Set rngFound = Rows(1).Find(varName, Cells(1, Columns.Count), xlValues, xlPart)
        If Not rngFound Is Nothing Then
            strFirst = rngFound.Address
            Do
                If rngDel Is Nothing Then Set rngDel = rngFound Else Set rngDel = Union(rngDel, rngFound)
                Set rngFound = Rows(1).Find(varName, rngFound, xlValues, xlPart)
            Loop While rngFound.Address <> strFirst
        End If
    Next varName
    If Not rngDel Is Nothing Then rngDel.EntireColumn.Delete
    Set rngFound = Nothing
    Set rngDel = Nothing
    Erase arrColumnNames
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,098
Office Version
  1. 365
Platform
  1. Windows
Change xlPart to xlWhole and you should be ok.
 

spsquirrel

New Member
Joined
Aug 2, 2019
Messages
3

ADVERTISEMENT

Thank you for your expertise, I just wasn't finding it.
Have a greatday
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,098
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1285097-exact-match-array.html#post5168694

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,881
Messages
5,598,636
Members
414,251
Latest member
oExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top