Macros to delete columns based on condition

Karthik-Excelsior

Active Member
Joined
Mar 4, 2011
Messages
313
Hi Excel gurus,

I have a file which I get every week which has upto 25 columns of data, however all that matters to me is just 4 columns of data: 'Campaign,' 'Status,' 'Budget,' & 'Cost.' (sample table can be found here: https://docs.google.com/spreadsheet...GVQV1BYOUpTN0UxWkVzc3ZXRGtGcGc&hl=en_US#gid=0 )

Can somebody help me with an excel macro that can identify only these 4 column headers (preferably in this order), and retain their values, and delete the rest of the columns.

Thanks a lot in advance!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Code:
Sub Delete_Columns()
    Dim c, i As Long
    Application.ScreenUpdating = False
    Columns("A:D").Insert
    c = Array("Campaign", "Status", "Budget", "Cost")
    For i = 1 To 4
        col = Rows("1:1").Find(c(i - 1), , , xlWhole, xlByColumns, xlNext, False).Column
        Columns(col).Cut Destination:=Columns(i)
    Next i
    Range(Cells(1, 5), Cells(1, Columns.Count).End(xlToLeft)).EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub
 

Karthik-Excelsior

Active Member
Joined
Mar 4, 2011
Messages
313
Hey Alphafrog,

Thanks a ton for your quick and accurate solution. While we're on this topic, can you also tell me how to run multiple macro codes consequently. For instance, I have to invoke another macro code (on the same worksheet) soon after this one runs. How do we do that ? (Something like a CALL statement? Am not too sure!) Can you help me with that?

Thanks!
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
You're welcome.

Yep. Call them.

Code:
Sub Macro_All()
    Call Macro1
    Call Macro2
    Call Macro3
End Sub
 

Karthik-Excelsior

Active Member
Joined
Mar 4, 2011
Messages
313

ADVERTISEMENT

Thanks a lot, AlphaFrog! You're simply SUPERB! :)

On a side note, I would like to gain expertise on excel VBA n Macros. Is there an online resource especially for beginners like me to learn and develop skills? It would be really helpful for people like me. I would be able to share my knowledge with my entire team here, who can benefit out of excel BIG time!

Best,
 

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046

ADVERTISEMENT

Hi,

I am unable to understand the following could you explain in a simple language to me.

Code:
col = Rows("1:1").Find(c(i - 1), , , xlWhole, xlByColumns, xlNext, False).Column
Range(Cells(1, 5), Cells(1, Columns.Count).End(xlToLeft)).EntireColumn.Delete

i tried running the code using F8 even then i couldn't figure it out.

If i = 1 in the above then c(1-1) becomes c(0)...does it mean the first element in the array i.e " campaign "?

what are xlwhole , xlby columsn , xlNext stand for?

In the second line ..what is Cells(1,columns.count) ?

Thank you for a nice code.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
If i = 1 in the above then c(1-1) becomes c(0)...does it mean the first element in the array i.e " campaign "?
Yes. The elements in c are indexed from 0 to 3


what are xlwhole , xlby columsn , xlNext stand for?
They are the arguments in the .Find method


In the second line ..what is Cells(1,columns.count) ?
It is the last physical cell in row 1 e.g. IV1 for Excel 2003 or earlier, or XFD1 in Excel 2007 or later.
This determines the last used cell in row 1...
Cells(1, Columns.Count).End(xlToLeft)

Finding the Last Used Cell in a Range
 

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Thank you for the explanation and i learned something new from you today.
 

Woffy

New Member
Joined
Oct 21, 2011
Messages
9
I would like to gain expertise on excel VBA n Macros. Is there an online resource especially for beginners like me to learn and develop skills?

I've found Excel VBA Programming for Dummies nice and accessible (I've got the book, but I think there's an online reference, too), but I've found it most useful for just making me aware of what's out there and what VBA can do in general. When I'm trying to do something specific I almost exclusively use these boards when I get stuck - most people have done the same silly thing as me before!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,271
Members
416,963
Latest member
samfuge

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