Macros to delete columns based on condition

[Karthik-Excelsior]

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!

 

[AlphaFrog]

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]

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]

You're welcome.

Yep. Call them.

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

 

[Karthik-Excelsior]

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,

 

[AlphaFrog]

Is there an online resource especially for beginners like me to learn and develop skills?

There are several, but I don't have any specific recommendation. Sorry.

 

[zaska]

Hi,

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

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]

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]

Thank you for the explanation and i learned something new from you today.

 

[Woffy]

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!