Removing Columns/Rows with Specified Text

[AStereotypicalGamer]

So I work for an IT reseller and deal with a lot of quotes and spreadsheets daily - recently I was moved to help cover another department and noticed that they were doing A LOT of manual work that seems unnecessary. I have cleaned most of it up and automated using AutoHotKey or other means but one thing I am still working on is trying to format their quotes with a few clicks of a button.

TL;DR, I am trying to create 2 VBA macros that will search the sheet and remove the rows and columns that contain the below headers:


ROW Headers

Net Quote Amount:, Total Fee %:, Fee Amount:, Quote Net Total and Fee Amount:, Notes:, Bill to Name:, Bill to ID:, Customer Number:, Created By:, Created Date(DD-Mon-YYYY):, Last Modified(DD-Mon-YYYY):, Total Errors/Warnings:, Severe Errors:, Warnings:, Channel:, Intended Use:, Currency:, Co-Term:, Co-Term Date(DD-Mon-YYYY):, Deal ID:, Partner Reference Number:, Earliest Price Protection End Date(DD-Mon-YYYY):, Flexible Invoicing Quote:, Solcat#:, Display Partner Discount:, Taxability Value:

COLUMN Headers

REVENUE SOURCE CODE, HOST ID, OLD/REPLACED SN, PRODUCT CATEGORY, PRODUCT PO, PRODUCT SO, SOURCE CONTRACT NUMBER, SOURCE SERVICE LEVEL, SOURCE SERVICE END DATE(DD-MON-YYYY), TAKEOVER TYPE, TAKEOVER LINE TYPE, PRICE PROTECTION BEGIN DATE(DD-MON-YYYY), PRICE PROTECTION END DATE(DD-MON-YYYY), GU ID, GU NAME, SITE ADDRESS LINE 3, INSTALL SITE CONTACT FIRST NAME, INSTALL SITE CONTACT LAST NAME, INSTALL SITE CONTACT EMAIL, INSTALL SITE CONTACT PHONE, DELIVERY METHOD, EDELIVERY EMAIL, PAK PREFERENCE, ROUTING SHIPPING PREFERENCE, SHIPPING SERVICELEVEL, SHIPPING CARRIER, SHIPPING CARRIER ACCTNUMBER, SERVICE LINE ID, SHIPTO SITE ID, SHIPTO CONTACT ID, SHIPTO NAME, SHIPTO ADDRESS1, SHIPTO ADDRESS2, SHIPTO ADDRESS3, SHIPTO CITY, SHIPTO STATE, SHIPTO COUNTRY, SHIPTO ZIP, SHIPTO FIRST NAME, SHIPTO LAST NAME, SHIPTO TELEPHONE, SHIPTO FAX, SHIPTO EMAIL, PERCENTAGE REFERENCE CURRENCY, % OF PRODUCT PRICE
PRODUCT LIST PRICE, STANDARD SERVICE DISCOUNT - USD%, EFFECTIVE TOTAL ADJUSTED AMOUNT, CREDIT ADJUSTMENT, ANNUAL SERVICE LIST PRICE

I have tried to piece together macros I have found here and elsewhere but they keep breaking or deleting things they shouldn't.

Thanks,

 

[Joe4]

Welcome to the Board!

Can you post some of the methods you have tried?
We may be able to take what you have done so far, and fix it up to get it to work for you.

 

[AStereotypicalGamer]

Welcome to the Board!

Can you post some of the methods you have tried?
We may be able to take what you have done so far, and fix it up to get it to work for you.

Thanks!


I tried using the below, but it seemed to always delete stuff in columns that did not contain the words:

Sub SmartnetFormat2()
Dim x As Integer
Dim cell As Range
Dim myRange As Range
Dim myDeleteData As String
Dim myDeleteColumns(256) As Boolean
'create a string of all data that if found, will cause a column delete
myDeleteData = "PRODUCT LIST PRICE, STANDARD SERVICE DISCOUNT - USD%, EFFECTIVE TOTAL ADJUSTED AMOUNT, CREDIT ADJUSTMENT, ANNUAL SERVICE LIST PRICE"
'use goto special to highlight all cells that contain a constant
Cells.SpecialCells(xlCellTypeConstants, 23).Select
Set myRange = Selection
For Each cell In myRange
'search to see if the cell contents is in the list
If InStr(myDeleteData, cell.Value) Then
'if so, then mark it for deletion
myDeleteColumns(cell.Column) = True
End If
Next cell
For x = 256 To 1 Step -1
'delete columns from the right, so you keep integrity of column numbers
If myDeleteColumns(x) = True Then
Columns(x).Delete
End If
Next x
End Sub

 

[Joe4]

I believe the issue is that you are not looking for exact entries. You essentially looking to see if your cell contains ANY of the words in your myDeleteData string
So, if your cell was just the word "LIST" or "AMOUNT" or "PRICE", it would mark a "hit".

I think it would be better to store each complete phrase you are looking for in an Array, and loop through the array to see if your cell entries matches any of the values in the Array exactly.