PDA

View Full Version : [SOLVED] 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
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
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
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
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 :doh: