Deleting all columns except columns with certain headings

[Jamlaudeen]

Hi All,

I am very new to VBA Macro. I have one requirement in my process. I need to Deleting all columns except columns with certain headings.Can you please any one help me.

Thanks,
Jamal.S

 

[Hui]

Something like :

For i = 13 To 1 Step -1 '1=Column A, 13=Column M
  If Cells(1, i) <> 1 Then Columns(i).Delete
Next

 

[Deepak]

Try this..

Option Explicit

Sub del()
Dim c As Long, i As Long

With Sheet1
    c = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

For i = c To 1 Step -1
    If Sheet1.Cells(1, i) <> "fff" Then Columns(i).Delete
Next i

End Sub

Oops Hui already did the same.

 

[Jamlaudeen]

Hi Deepak,

Thanks for your help, But My requirement is I have some headers like Student Name, Mark list, Percentage,Total,Roll Number etc ....... But I need to Keep few header column only apart from that should deleted.

Thanks,
Jamal.S

 

[Deepak]

Check with this!

Sub del_col()
Dim c As Long, i As Long, header As Range

With Sheet1
    c = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
'What's need to keep! for the safe side put the same on another worksheet
Set header = Sheet2.[A1:A10]

For i = c  To 1 Step -1
    If IsError(Application.Match(Sheet1.Cells(1, i), header, 0)) Then Columns(i).Delete
Next i

 

[Jon von der Heyden]

I've become quite a fan of using array formulae to construct arrays in VBA. I find that they often prove more efficient than looping. So with that in mind my contribution for deleting columns based on criteria is as follows:

1. Assume that headings are in Sheet1!A1:V1
2. Assume that the criteria values are in Sheet2!A1:A3 (matching headers flagged for deletion)

Public Sub DeleteCols()
    Dim varCols As Variant
    Const strFormula As String = "if(isnumber(match(A1:V1,Sheet2!A1:A3,0)),address(1,column(A1:V1)))"
   
    varCols = Filter(Evaluate(strFormula), False, False)
   
    Range(Join$(varCols, ",")).EntireColumn.Delete
End Sub

Regards
Jon

 

[Debraj]

Hi Jon..

Its an great pleasure to see you here..

Welcome to the forum..

 

[Jon von der Heyden]

Thanks for the welcome Debraj, it's an honour to be part of this forum.