Complicated Macro Question

E

enewton23

New Member
Joined
May 31, 2012
Messages
4
Hi all,

I have a very complicated (at least for me) question for anyone who's up for a challenge. Thank you in advance for your patience during this long explanation.

I frequently download spreadsheets of data from Google AdWords and format the tables to send to clients. For those unfamiliar with AdWords, there are a total of 34 columns available within the interface from which one can receive reporting. These include things such as cost, clicks, impressions, etc. When downloaded to excel, they appear in roughly the same format, with column headers starting in row two and the data following thereafter.

The formatting involves taking out certain columns of data while leaving others. This can become very time consuming, so I thought a macro would be valuable. Here is where it gets complicated: I work with several client accounts and while the names of the column headers are consistent across all accounts, their locations on the spreadsheet are not. For example, one report may place the "Cost" column in column B, while another may place it in column C. As you can see, this is a referencing nightmare. In short, I want to be able to download a report from AdWords for any client, and in the stroke of one shortcut key, delete all the unnecessary columns, regardless of their location in the spreadsheet.

The closest I have come is by choosing relative reference and then individually "finding" (cntrl f) each header that I don't need and deleting it from the spreadsheet. This works great except for one more problem: every client only selects certain columns out of the 34 available. Whichever of the 34 they view in AdWords are those that are downloaded to excel. So of each report, one may include impressions cost and clicks, while another may only include cost and impressions, but not clicks. This means that if I do cntrl f and find and delete the "clicks" column, for example, on an account that doesn't have a clicks column, then I pull an error code. Please realize, however, that the columns I need to delete are all the same, across all accounts - they are not specific to each account. It is just the columns available for viewing in the spreadsheet that vary.

This is where I'm stuck, and this is why I'm reaching out to anyone who could offer any assistance/advice. Truth is, I'm not even sure if macros are capable of achieving such complicated functions. Please let me know if there is any part of this you do not understand. Below is an example of the code I'm using with the error code where it tried to delete a column that didn't exist.

***Ultimately, I really just need to figure out a way to make a list of columns I want to be deleted and have excel delete those that do exist in the spreadsheet while ignoring those that do not exist in the spreadsheet.***​

Thank you in advance for all your help!

' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+i
'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Cells.Find(What:="campaign state", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="budget", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="status", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="cost", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Avg. CPC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Lost IS (budget)", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Lost IS (rank)", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Cells.Find(What:="Avg. CPM", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Columns("L:AA").Select
Selection.Delete Shift:=xlToLeft
Range("H5").Select
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Good job explaining the problem...

not even sure if macros are capable of achieving such complicated functions. :rolleyes:
Click to expand...

this is how mine would look...

Code: 
Sub CleanUp()
Dim searches
Dim found As Range

Application.ScreenUpdating = False

searches = Array("campaign state", "budget", "status", "cost", "Avg. CPC", "Lost IS (budget)", "Lost IS (rank)", "Avg. CPM")

With ActiveSheet
    .Cells(1, 1).Activate

    For i = 0 To UBound(searches)
    
    Set found = .Cells.Find(What:=searches(i), _
                After:=ActiveCell, _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)
            If Not found Is Nothing Then
                If found.Row = 2 Then found.EntireColumn.Delete
                Set found = Nothing
            End If
    
    Next i

Columns("L:AA").Delete Shift:=xlToLeft

End With

Application.ScreenUpdating = True

End Sub
 
Upvote 0
enewton23,


Welcome to the MrExcel forum.


Sample raw data with the column titles always in row 2:


Excel Workbook
ABCDEFGHIJKLMNOP
2campaign stateBbudgetDstatusFcostHAvg. CPCJLost IS (budget)LLost IS (rank)NAvg. CPMP
Sheet1





After the macro:


Excel Workbook
ABCDEFGHIJKLMNOP
2BDFHJLNP
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub DeleteColumns()
' hiker95, 05/31/1021
' http://www.mrexcel.com/forum/showthread.php?t=639110
Dim DelCol, c As Long, fc As Long
Application.ScreenUpdating = False
DelCol = Array("campaign state", "budget", "status", "cost", "Avg. CPC", "Lost IS (budget)", "Lost IS (rank)", "Avg. CPM")
For c = LBound(DelCol) To UBound(DelCol)
  fc = 0
  On Error Resume Next
  fc = Application.Match(DelCol(c), Rows(2), 0)
  On Error GoTo 0
  If fc > 0 Then Columns(fc).Delete
Next c
Columns("L:AA").Delete
Range("H5").Select
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the DeleteColumns macro.
 
Upvote 0
Wow. That's incredible. Just what I was looking for - thank you.

Now I have one last request if you're up for it. I'd really like to implement some formatting into the macro if possible. Here is what I would need: clicks, impressions and conv. (many-per-click) to be a number with 1000 separator and 0 decimal places; CTR and impr. share to be a percentage with 2 decimal places; avg. position to be a number with one decimal place and no 1000 separator; cost / conv. (many-per-click) to be a currency with 2 decimal places. I did the find and replace thing again today, so I'm just facing the same problem with it pulling an error code when it doesn't find a certain column in some accounts. You can see the code below.

I just put it before the code you sent me yesterday. Also, I made some slight adjustments to the code you sent over in the DelCol = Array section to accommodate to all 34 available columns.

Thank you for all your help. I'm relieved to finally have this coming together. Please let me know if you have any questions.

Option Explicit
Sub DeleteColumns()
' hiker95, 05/31/1021
' http://www.mrexcel.com/forum/showthread.php?t=639110
Cells.Find(What:="clicks", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "#,##0"
Cells.Find(What:="impressions", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(16, 2).Range("A1").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "#,##0"
ActiveCell.Offset(17, 3).Range("A1").Select
Cells.Find(What:="ctr", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(17, 3).Range("A1").Select
Cells.Find(What:="avg. position", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.0"
Cells.Find(What:="impr. share", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(17, 2).Range("A1").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(17, 3).Range("A1").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(17, 2).Range("A1").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(18, 1).Range("A1").Select
Cells.Find(What:="conv. (many-per-click)", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "#,##0"
ActiveCell.Offset(19, 1).Range("A1").Select
Cells.Find(What:="cost / conv. (many-per-click)", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "$#,##0.00"
ActiveCell.Offset(22, -2).Range("A1").Select
Dim DelCol, c As Long, fc As Long
Application.ScreenUpdating = False
DelCol = Array("campaign state", "budget", "status", "cost", "Avg. CPC", "Lost IS (budget)", "Lost IS (rank)", "Avg. CPM", "total cost", "invalid clicks", "conv. (1-per-click)", "cost / conv. (1-per-click)", "conv. rate (1-per-click)", "view-through conv.", "conv. rate (many-per-click)", "total conv. value", "conv. value / cost", "conv. value / click", "value / conv. (1-per-click)", "value / conv. (many-per-click)", "labels", "phone impressions", "phone calls", "ptr", "phone cost", "avg. cpp", "exact match is", "relative ctr")
For c = LBound(DelCol) To UBound(DelCol)
fc = 0
On Error Resume Next
fc = Application.Match(DelCol(c), Rows(2), 0)
On Error GoTo 0
If fc > 0 Then Columns(fc).Delete
Next c
Range("H5").Select
Application.ScreenUpdating = True
End Sub
 
Upvote 0
enewton23,

Thanks for the feedback.

You are very welcome. Glad I could help.


Moving forward, if posting VBA code, please use Code Tags - like this:

[code]

'Paste your code here.

[/code]


Now I have one last request if you're up for it. I'd really like to implement some formatting into the macro if possible. Here is what I would need: clicks, impressions and conv. (many-per-click) to be a number with 1000 separator and 0 decimal places; CTR and impr. share to be a percentage with 2 decimal places; avg. position to be a number with one decimal place and no 1000 separator; cost / conv. (many-per-click) to be a currency with 2 decimal places. I did the find and replace thing again today, so I'm just facing the same problem with it pulling an error code when it doesn't find a certain column in some accounts. You can see the code below.
Click to expand...

Let me look at your code.
 
Upvote 0
Here is the code with code tags in case you wanted it in this format for this time as well. Moving forward, I'll be sure to always include them. Again, thank you for your help!

Code: 
Option Explicit
Sub DeleteColumns()
' hiker95, 05/31/1021
' http://www.mrexcel.com/forum/showthread.php?t=639110
Cells.Find(What:="clicks", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "#,##0"
Cells.Find(What:="impressions", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(16, 2).Range("A1").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "#,##0"
ActiveCell.Offset(17, 3).Range("A1").Select
Cells.Find(What:="ctr", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(17, 3).Range("A1").Select
Cells.Find(What:="avg. position", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.0"
Cells.Find(What:="impr. share", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(17, 2).Range("A1").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(17, 3).Range("A1").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(17, 2).Range("A1").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(18, 1).Range("A1").Select
Cells.Find(What:="conv. (many-per-click)", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "#,##0"
ActiveCell.Offset(19, 1).Range("A1").Select
Cells.Find(What:="cost / conv. (many-per-click)", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "$#,##0.00"
ActiveCell.Offset(22, -2).Range("A1").Select
Dim DelCol, c As Long, fc As Long
Application.ScreenUpdating = False
DelCol = Array("campaign state", "budget", "status", "cost", "Avg. CPC", "Lost IS (budget)", "Lost IS (rank)", "Avg. CPM", "total cost", "invalid clicks", "conv. (1-per-click)", "cost / conv. (1-per-click)", "conv. rate (1-per-click)", "view-through conv.", "conv. rate (many-per-click)", "total conv. value", "conv. value / cost", "conv. value / click", "value / conv. (1-per-click)", "value / conv. (many-per-click)", "labels", "phone impressions", "phone calls", "ptr", "phone cost", "avg. cpp", "exact match is", "relative ctr")
For c = LBound(DelCol) To UBound(DelCol)
fc = 0
On Error Resume Next
fc = Application.Match(DelCol(c), Rows(2), 0)
On Error GoTo 0
If fc > 0 Then Columns(fc).Delete
Next c
Range("H5").Select
Application.ScreenUpdating = True
End Sub
 
Upvote 0
enewton23,

OK, I took your macro code and created another array UpdCol.

Then I created Select Case code with those column titles.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub Update_and_DeleteColumns()
' hiker95, 06/01/1021
' http://www.mrexcel.com/forum/showthread.php?t=639110
Dim UpdCol, DelCol, c As Long, fc As Long
Application.ScreenUpdating = False
UpdCol = Array("clicks", "impressions", "ctr", "avg.Position", "impr.share", "conv. (many-per-click)", "cost / conv. (many-per-click)")
For c = LBound(UpdCol) To UBound(UpdCol)
  fc = 0
  On Error Resume Next
  fc = Application.Match(UpdCol(c), Rows(2), 0)
  On Error GoTo 0
  If fc > 0 Then
    Select Case UpdCol(c)
      Case "clicks"
        Cells.Find(What:="clicks", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
          :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False).Activate
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "#,##0"
      Case "impressions"
        Cells.Find(What:="impressions", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
          :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False).Activate
        ActiveCell.Offset(16, 2).Range("A1").Select
        Cells.FindNext(After:=ActiveCell).Activate
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "#,##0"
        ActiveCell.Offset(17, 3).Range("A1").Select
      Case "ctr"
        Cells.Find(What:="ctr", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
          xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
          , SearchFormat:=False).Activate
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "0.00%"
        ActiveCell.Offset(17, 3).Range("A1").Select
      Case "avg.Position"
        Cells.Find(What:="avg. position", After:=ActiveCell, LookIn:=xlFormulas, _
          LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "0.0"
      Case "impr.share"
        Cells.Find(What:="impr. share", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
          :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False).Activate
        ActiveCell.Offset(17, 2).Range("A1").Select
        Cells.FindNext(After:=ActiveCell).Activate
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "0.00%"
        ActiveCell.Offset(17, 3).Range("A1").Select
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "0.00%"
        ActiveCell.Offset(17, 2).Range("A1").Select
        Cells.FindNext(After:=ActiveCell).Activate
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "0.00%"
        ActiveCell.Offset(18, 1).Range("A1").Select
      Case "conv. (many-per-click)"
        Cells.Find(What:="conv. (many-per-click)", After:=ActiveCell, LookIn:= _
          xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
          xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "#,##0"
        ActiveCell.Offset(19, 1).Range("A1").Select
      Case "cost / conv. (many-per-click)"
        Cells.Find(What:="cost / conv. (many-per-click)", After:=ActiveCell, _
          LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "$#,##0.00"
        ActiveCell.Offset(22, -2).Range("A1").Select
    End Select
  End If
Next c
DelCol = Array("campaign state", "budget", "status", "cost", "Avg. CPC", "Lost IS (budget)", "Lost IS (rank)", "Avg. CPM")
For c = LBound(DelCol) To UBound(DelCol)
  fc = 0
  On Error Resume Next
  fc = Application.Match(DelCol(c), Rows(2), 0)
  On Error GoTo 0
  If fc > 0 Then Columns(fc).Delete
Next c
Columns("L:AA").Delete
Range("H5").Select
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the Update_and_DeleteColumns macro.
 
Upvote 0
That's it! Worked perfectly. Thank you so much. I'm amazed at how quickly you've been able to solve this problem. This will definitely help save lots of time. Have an excellent day.
 
Upvote 0
enewton23,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.


It looks like I did not copy over the updated DelCol array that you had updated.

Please replace what was in my last post with the below line of code.


Code: 
DelCol = Array("campaign state", "budget", "status", "cost", "Avg. CPC", "Lost IS (budget)", "Lost IS (rank)", "Avg. CPM", "total cost", "invalid clicks", "conv. (1-per-click)", "cost / conv. (1-per-click)", "conv. rate (1-per-click)", "view-through conv.", "conv. rate (many-per-click)", "total conv. value", "conv. value / cost", "conv. value / click", "value / conv. (1-per-click)", "value / conv. (many-per-click)", "labels", "phone impressions", "phone calls", "ptr", "phone cost", "avg. cpp", "exact match is", "relative ctr")
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Looping code sequence
Replies
5
Views
148
FrenchCelt
F
P
  • Solved
Add an IF Statement
2
Replies
10
Views
371
Joe4
Joe4
S
  • Question
Excel macros extremely slow
Replies
4
Views
187
Shloime
S
M
  • Question
Excel VBA Set Activecell Formula With String
Replies
1
Views
447
Special-K99
S
M
  • Locked
  • Question
Sumproduct Formula in VBA
Replies
3
Views
488
Fluff
Fluff

Forum statistics

Threads
1,214,922
Messages
6,122,281
Members
449,075
Latest member
staticfluids

Share this page

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
Back
Top