vba delete columns based on header value

[t6116ks]

Hello,
I would like to call a Sub anywhere in my workbook to delete all columns containing "*" in the header of one specific worksheet "data".
several postings I tried work ok on a few columns, buy my 'data" sheet contains about 200 columns (so the sub gets stuck and I have to abort...)

I would appreciate if you can lead me through it.
Thanks!

 

[alansidman]

try this

Option Explicit


Sub DelAsterisk()
    Dim i As Long, lc As Long
    'Assumes header is in row 1
    With Sheets("data")
        .Cells(1, Columns.Count).End(xlToLeft).Column
        For i = lc To 1 Step -1
            If InStr(.Cells(1, i), "*") > 0 Then
                .Cells(1, i).EntireColumn.Delete
            End If
        Next i
    End With
End Sub

 

[Peter_SSs]

Alan, looks like a slight omission in your code?

lc = .Cells(1, Columns.Count).End(xlToLeft).Column

 

[alansidman]

Thank you, Peter. Nice catch. I guess i should test code before posting.

 

[t6116ks]

Thank you so much Alan and Peter !
It does work, however it takes a long time spinning and afraid with 150+ columns to go through, it seems it is stuck in a loop.
Is there a way to speed up?
Thanks again!

 

[t6116ks]

One more key info: If I trigger Macro from a different sheet, it spins for ever and Excel stops responding.
So Cntl-Alt-Del was only way out...

Thanks again for your support!

 

[Peter_SSs]

It does work, however it takes a long time spinning and afraid with 150+ columns to go through, it seems it is stuck in a loop.

150 columns isn't many so it sounds like you have some other code going on or lots of formulas recalculation each time a column is deleted. See if adding the blue sections makes a difference.

Sub DelAsterisk()
    Dim i As Long, lc As Long, lCalc As Long
    'Assumes header is in row 1
    With Application
      lCalc = .Calculation
      .DisplayAlerts = False
      .Calculation = xlCalculationManual
      .EnableEvents = False
    End With
    With Sheets("data")
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        For i = lc To 1 Step -1
            If InStr(.Cells(1, i), "*") > 0 Then
                .Cells(1, i).EntireColumn.Delete
            End If
        Next i
    End With
    With Application
      .DisplayAlerts = True
      .Calculation = lCalc
      .EnableEvents = True
    End With
End Sub

 

[t6116ks]

Thanks a lot Peter!
You are absolutely correct. I have another sheet looking at every cell in "data" and making calculation.
So I will have to change my strategy on the calculation.
Thanks again. You guys are awsome!

 

[craigexcel]

Great posts by both Alan and Peter. Thanks for those so I, too, can continue to learn.

Peter -- it looks like a small, but key, letter was inadvertently omitted from this part of the code:

  [COLOR=#0000cd][B]  With Application
      .DisplayAlerts = True
      .Calculation = [/B][/COLOR][COLOR=#FF0000][B]x[/B][/COLOR][COLOR=#0000cd][B]lCalc
      .EnableEvents = True
    End With[/B][/COLOR]

I think the "x" needs to be added. Does that look right?

 

[Fluff]

Peter_SSs code was correct.
lCalc is a variable that was assigned a value with

[COLOR=#0000CD][B] lCalc = .Calculation[/B][/COLOR]

That stores the current calculation setting at the beginning of the macro & resets to that value at the end.