ForEach Loop Trouble

**dkmorris90** · 05-16-2018, 07:23 PM

I am working on some code to delete columns, based on their headers, from a spreadsheet. I built out an array for certain column names that i need deleted. The issue i am having is that when loop is executed, if a column name does not appear from the array than the program stops running.

I need the statement to look at each of the items in the array, delete the column if the header is in the array, or skip that item and move on to the next in the array.

Dim Item As Variant
Dim A As Range

ColumnsToRemove = Array("Agreement Name", "Last Transaction Date", "Sender Email", "Sender Group", "Sender Company", "Sender Title" _
& "Workflow", "Type", "'# of Participants", "'# of Completed Signatures", "Remaining Signatures", "Message", "To 2", "To 3", "Recipient 1 Email" _
& "Recipient 1 Role", "Recipient 1 Company", "Recipient 1 Title", "Recipient 1 Completed Date", "Recipient 1 IP Address" _
& "Sender IP Address", "Sender Timezone", "Sender Device", "Form", "Recipient 1 Timezone", "Started Date", "Recipient 1 Started Date", "Recipient 1 Viewed Date" _
& "Recipient 1 Signed On", "Recipient 1 Rejection Reason", "Recipient 2 Name", "Recipient 2 Email", "Recipient 2 Role", "Recipient 2 Company", "Recipient 2 Title" _
& "Recipient 2 Completed Date", "Recipient 3 Name", "Recipient 3 Email", "Recipient 3 Role", "Recipient 3 Company", "Recipient 3 Title" _
& "Recipient 3 Completed Date", "Recipient 3 IP Address", "Recipient 3 Timezone", "Recipient 3 Started Date", "Recipient 3 Viewed Date" _
& "Recipient 3 Signed On", "Recipient 3 Rejection Reason", "Recipient 2 IP Address", "Recipient 2 Timezone", "Recipient 2 Started Date" _
& "Recipient 2 Viewed Date", "Recipient 2 Signed On", "Recipient 2 Rejection Reason", "Next Recipient Email", "Next Recipient Role" _
& "CS Custom Shape Art Checkbox", "CS Business Card Holder QTY", "Mail To Recipient Company Name", "Mail To Recipient Attention To" _
& "No. of Years", "CU Special Instructions Line 1", "Mail To Recipient Zip", "CU Business Card Holder Checkbox", "Recipient Attention To" _
& "Binder Color", "DATE", "CU Business Card Holder QTY", "CS Special Instructions Line 1", "TERRITORY MANAGER" _
& "RS Special Instructions Line 2", "CS Brochure Holders Checkbox", "Back Patch QTY", "CS Business Card Holders Checkbox" _
& "CS Brochure Holder QTY", "Fax Area Code", "DISPLAY AREA IN FACILITY", "email2", "Ship to Rep: No", "Mail To Recipient Street Address" _
& "Mail To Recipient City", "Mail To Recipient State", "HS Art Only Checkbox", "TOTAL MAGAZINES", "Fax Number", "CS Pedestal Unit Checkbox", "BACK PATCHES TYPE")

For Each Item In ColumnsToRemove

Set A = Rows(1).Find(What:=ColumnsToRemove, LookIn:=xlValues, _
lookat:=xlPart)
If A Is Nothing Then
Debug.Print "Nothing found"
ElseIf Not A Is Nothing Then
A.EntireColumn.Delete
End If
Next

**Kenneth Hobs** · 05-16-2018, 08:22 PM

Welcome to the forum! Please use code tags.

Set A = Rows(1).Find(What:=Item, LookIn:=xlValues, _
lookat:=xlPart)

**dkmorris90** · 05-16-2018, 08:41 PM

Originally Posted by Kenneth Hobs

Welcome to the forum! Please use code tags.

Set A = Rows(1).Find(What:=Item, LookIn:=xlValues, _
lookat:=xlPart)

Thanks for the response Kenneth. this is first time posting and wasn't sure how to use the tags. I made the changes that you referenced but am still having issues. I have attached the file to this thread if you wanted to take a look.

**Paul_Hossler** · 05-17-2018, 07:53 AM

When deleting rows and columns, it's better to start from the bottom and delete your way to the top, or from the right and delete your way to the left

Another way

Option Explicit

Private Sub CommandButton1_Click()
    Dim ColumnsToRemove As Variant
    Dim Item As Variant
    Dim i As Long

    ColumnsToRemove = Array("Agreement Name", "Last Transaction Date", "Sender Email", "Sender Group", "Sender Company", "Sender Title" _
        & "Workflow", "Type", "'# of Participants", "'# of Completed Signatures", "Remaining Signatures", "Message", "To 2", "To 3", "Recipient 1 Email" _
        & "Recipient 1 Role", "Recipient 1 Company", "Recipient 1 Title", "Recipient 1 Completed Date", "Recipient 1 IP Address" _
        & "Sender IP Address", "Sender Timezone", "Sender Device", "Form", "Recipient 1 Timezone", "Started Date", "Recipient 1 Started Date", "Recipient 1 Viewed Date" _
        & "Recipient 1 Signed On", "Recipient 1 Rejection Reason", "Recipient 2 Name", "Recipient 2 Email", "Recipient 2 Role", "Recipient 2 Company", "Recipient 2 Title" _
        & "Recipient 2 Completed Date", "Recipient 3 Name", "Recipient 3 Email", "Recipient 3 Role", "Recipient 3 Company", "Recipient 3 Title" _
        & "Recipient 3 Completed Date", "Recipient 3 IP Address", "Recipient 3 Timezone", "Recipient 3 Started Date", "Recipient 3 Viewed Date" _
        & "Recipient 3 Signed On", "Recipient 3 Rejection Reason", "Recipient 2 IP Address", "Recipient 2 Timezone", "Recipient 2 Started Date" _
        & "Recipient 2 Viewed Date", "Recipient 2 Signed On", "Recipient 2 Rejection Reason", "Next Recipient Email", "Next Recipient Role" _
        & "CS Custom Shape Art Checkbox", "CS Business Card Holder QTY", "Mail To Recipient Company Name", "Mail To Recipient Attention To" _
        & "No. of Years", "CU Special Instructions Line 1", "Mail To Recipient Zip", "CU Business Card Holder Checkbox", "Recipient Attention To" _
        & "Binder Color", "DATE", "CU Business Card Holder QTY", "CS Special Instructions Line 1", "TERRITORY MANAGER" _
        & "RS Special Instructions Line 2", "CS Brochure Holders Checkbox", "Back Patch QTY", "CS Business Card Holders Checkbox" _
        & "CS Brochure Holder QTY", "Fax Area Code", "DISPLAY AREA IN FACILITY", "email2", "Ship to Rep: No", "Mail To Recipient Street Address" _
        & "Mail To Recipient City", "Mail To Recipient State", "HS Art Only Checkbox", "TOTAL MAGAZINES", "Fax Number", "CS Pedestal Unit Checkbox", "BACK PATCHES TYPE")
    
    
    For Each Item In ColumnsToRemove
        On Error Resume Next
        Cells(1, Application.WorksheetFunction.Match(Item, Rows(1), 0)).Value = True
        On Error GoTo 0
    Next
        
    On Error Resume Next
    Rows(1).SpecialCells(xlCellTypeConstants, xlLogical).EntireColumn.Delete
    On Error GoTo 0
        
        
End Sub

**dkmorris90** · 05-17-2018, 10:45 AM

Originally Posted by Paul_Hossler

When deleting rows and columns, it's better to start from the bottom and delete your way to the top, or from the right and delete your way to the left

Another way

Option Explicit

Private Sub CommandButton1_Click()
    Dim ColumnsToRemove As Variant
    Dim Item As Variant
    Dim i As Long

    ColumnsToRemove = Array("Agreement Name", "Last Transaction Date", "Sender Email", "Sender Group", "Sender Company", "Sender Title" _
        & "Workflow", "Type", "'# of Participants", "'# of Completed Signatures", "Remaining Signatures", "Message", "To 2", "To 3", "Recipient 1 Email" _
        & "Recipient 1 Role", "Recipient 1 Company", "Recipient 1 Title", "Recipient 1 Completed Date", "Recipient 1 IP Address" _
        & "Sender IP Address", "Sender Timezone", "Sender Device", "Form", "Recipient 1 Timezone", "Started Date", "Recipient 1 Started Date", "Recipient 1 Viewed Date" _
        & "Recipient 1 Signed On", "Recipient 1 Rejection Reason", "Recipient 2 Name", "Recipient 2 Email", "Recipient 2 Role", "Recipient 2 Company", "Recipient 2 Title" _
        & "Recipient 2 Completed Date", "Recipient 3 Name", "Recipient 3 Email", "Recipient 3 Role", "Recipient 3 Company", "Recipient 3 Title" _
        & "Recipient 3 Completed Date", "Recipient 3 IP Address", "Recipient 3 Timezone", "Recipient 3 Started Date", "Recipient 3 Viewed Date" _
        & "Recipient 3 Signed On", "Recipient 3 Rejection Reason", "Recipient 2 IP Address", "Recipient 2 Timezone", "Recipient 2 Started Date" _
        & "Recipient 2 Viewed Date", "Recipient 2 Signed On", "Recipient 2 Rejection Reason", "Next Recipient Email", "Next Recipient Role" _
        & "CS Custom Shape Art Checkbox", "CS Business Card Holder QTY", "Mail To Recipient Company Name", "Mail To Recipient Attention To" _
        & "No. of Years", "CU Special Instructions Line 1", "Mail To Recipient Zip", "CU Business Card Holder Checkbox", "Recipient Attention To" _
        & "Binder Color", "DATE", "CU Business Card Holder QTY", "CS Special Instructions Line 1", "TERRITORY MANAGER" _
        & "RS Special Instructions Line 2", "CS Brochure Holders Checkbox", "Back Patch QTY", "CS Business Card Holders Checkbox" _
        & "CS Brochure Holder QTY", "Fax Area Code", "DISPLAY AREA IN FACILITY", "email2", "Ship to Rep: No", "Mail To Recipient Street Address" _
        & "Mail To Recipient City", "Mail To Recipient State", "HS Art Only Checkbox", "TOTAL MAGAZINES", "Fax Number", "CS Pedestal Unit Checkbox", "BACK PATCHES TYPE")
    
    
    For Each Item In ColumnsToRemove
        On Error Resume Next
        Cells(1, Application.WorksheetFunction.Match(Item, Rows(1), 0)).Value = True
        On Error GoTo 0
    Next
        
    On Error Resume Next
    Rows(1).SpecialCells(xlCellTypeConstants, xlLogical).EntireColumn.Delete
    On Error GoTo 0
        
        
End Sub

Hey Paul. Thanks for getting back to me. The code that you posted is changing some of the column headers but not deleting the columns that i need deleted (See column "Workflow"). Any ideas?

**p45cal** · 05-17-2018, 11:50 AM

The line which assigns ColumnsToRemove shouldn't have those & symbols in, and there should be a comma after each close-quote:

    ColumnsToRemove = Array("Agreement Name", "Last Transaction Date", "Sender Email", "Sender Group", "Sender Company", "Sender Title", _
         "Workflow", "Type", "'# of Participants", "'# of Completed Signatures", "Remaining Signatures", "Message", "To 2", "To 3", "Recipient 1 Email", _
         "Recipient 1 Role", "Recipient 1 Company", "Recipient 1 Title", "Recipient 1 Completed Date", "Recipient 1 IP Address", _
         "Sender IP Address", "Sender Timezone", "Sender Device", "Form", "Recipient 1 Timezone", "Started Date", "Recipient 1 Started Date", "Recipient 1 Viewed Date", _
         "Recipient 1 Signed On", "Recipient 1 Rejection Reason", "Recipient 2 Name", "Recipient 2 Email", "Recipient 2 Role", "Recipient 2 Company", "Recipient 2 Title", _
         "Recipient 2 Completed Date", "Recipient 3 Name", "Recipient 3 Email", "Recipient 3 Role", "Recipient 3 Company", "Recipient 3 Title", _
         "Recipient 3 Completed Date", "Recipient 3 IP Address", "Recipient 3 Timezone", "Recipient 3 Started Date", "Recipient 3 Viewed Date", _
         "Recipient 3 Signed On", "Recipient 3 Rejection Reason", "Recipient 2 IP Address", "Recipient 2 Timezone", "Recipient 2 Started Date", _
         "Recipient 2 Viewed Date", "Recipient 2 Signed On", "Recipient 2 Rejection Reason", "Next Recipient Email", "Next Recipient Role", _
         "CS Custom Shape Art Checkbox", "CS Business Card Holder QTY", "Mail To Recipient Company Name", "Mail To Recipient Attention To", _
         "No. of Years", "CU Special Instructions Line 1", "Mail To Recipient Zip", "CU Business Card Holder Checkbox", "Recipient Attention To", _
         "Binder Color", "DATE", "CU Business Card Holder QTY", "CS Special Instructions Line 1", "TERRITORY MANAGER", _
         "RS Special Instructions Line 2", "CS Brochure Holders Checkbox", "Back Patch QTY", "CS Business Card Holders Checkbox", _
         "CS Brochure Holder QTY", "Fax Area Code", "DISPLAY AREA IN FACILITY", "email2", "Ship to Rep: No", "Mail To Recipient Street Address", _
         "Mail To Recipient City", "Mail To Recipient State", "HS Art Only Checkbox", "TOTAL MAGAZINES", "Fax Number", "CS Pedestal Unit Checkbox", "BACK PATCHES TYPE")

It was concatenating headers; "Sender TitleWorkflow" was one.

**dkmorris90** · 05-17-2018, 12:09 PM

Originally Posted by p45cal

The line which assigns ColumnsToRemove shouldn't have those & symbols in, and there should be a comma after each close-quote:

    ColumnsToRemove = Array("Agreement Name", "Last Transaction Date", "Sender Email", "Sender Group", "Sender Company", "Sender Title", _
         "Workflow", "Type", "'# of Participants", "'# of Completed Signatures", "Remaining Signatures", "Message", "To 2", "To 3", "Recipient 1 Email", _
         "Recipient 1 Role", "Recipient 1 Company", "Recipient 1 Title", "Recipient 1 Completed Date", "Recipient 1 IP Address", _
         "Sender IP Address", "Sender Timezone", "Sender Device", "Form", "Recipient 1 Timezone", "Started Date", "Recipient 1 Started Date", "Recipient 1 Viewed Date", _
         "Recipient 1 Signed On", "Recipient 1 Rejection Reason", "Recipient 2 Name", "Recipient 2 Email", "Recipient 2 Role", "Recipient 2 Company", "Recipient 2 Title", _
         "Recipient 2 Completed Date", "Recipient 3 Name", "Recipient 3 Email", "Recipient 3 Role", "Recipient 3 Company", "Recipient 3 Title", _
         "Recipient 3 Completed Date", "Recipient 3 IP Address", "Recipient 3 Timezone", "Recipient 3 Started Date", "Recipient 3 Viewed Date", _
         "Recipient 3 Signed On", "Recipient 3 Rejection Reason", "Recipient 2 IP Address", "Recipient 2 Timezone", "Recipient 2 Started Date", _
         "Recipient 2 Viewed Date", "Recipient 2 Signed On", "Recipient 2 Rejection Reason", "Next Recipient Email", "Next Recipient Role", _
         "CS Custom Shape Art Checkbox", "CS Business Card Holder QTY", "Mail To Recipient Company Name", "Mail To Recipient Attention To", _
         "No. of Years", "CU Special Instructions Line 1", "Mail To Recipient Zip", "CU Business Card Holder Checkbox", "Recipient Attention To", _
         "Binder Color", "DATE", "CU Business Card Holder QTY", "CS Special Instructions Line 1", "TERRITORY MANAGER", _
         "RS Special Instructions Line 2", "CS Brochure Holders Checkbox", "Back Patch QTY", "CS Business Card Holders Checkbox", _
         "CS Brochure Holder QTY", "Fax Area Code", "DISPLAY AREA IN FACILITY", "email2", "Ship to Rep: No", "Mail To Recipient Street Address", _
         "Mail To Recipient City", "Mail To Recipient State", "HS Art Only Checkbox", "TOTAL MAGAZINES", "Fax Number", "CS Pedestal Unit Checkbox", "BACK PATCHES TYPE")

It was concatenating headers; "Sender TitleWorkflow" was one.

That was it. Thanks a ton!

**Paul_Hossler** · 05-17-2018, 04:03 PM

Originally Posted by p45cal

The line which assigns ColumnsToRemove shouldn't have those & symbols in, and there should be a comma after each close-quote:It was concatenating headers; "Sender TitleWorkflow" was one.

Darn … glossed right over that

Thread: ForEach Loop Trouble

Thread Tools

Display

ForEach Loop Trouble

Posting Permissions