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!

 

[Karthik-Excelsior]

Hey Woofy,

Thanks for the recommendation. And yes! totally agree with you, whenever I get stuck, i always look up to the mxexcel forum.. we've an amazing bunch highly-talented excel folks here!

 

[Rick Rothstein]

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

I'm not sure, but I think this variation on your macro might run quicker...

Sub Delete_Columns()
    Dim X As Long, LastCol As Long, LastRow As Long, Keep() As String
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    Keep = Split("Campaign Status Budget Cost")
    For X = 1 To 4
        Cells(1, LastCol + X).Resize(LastRow).Value = Intersect(Rows("1:" & LastRow), Rows(1).Find(Keep(X - 1), , , xlWhole, xlByColumns, xlNext, False).EntireColumn).Value
    Next
    Columns(1).Resize(, LastCol).Delete
    Application.ScreenUpdating = True
End Sub

Note that I used the Split function instead of the Array function because its lower bound is independent of any Option Base setting (the lower bound is always 0 for the Split function).

 

[zaska]

Sir,

Thanks for another code can you please explain one line

 Cells(1, LastCol + X).Resize(LastRow).Value = Intersect(Rows("1:" & LastRow)

Resize , intersect are really confusing for me.

Thank you