VBA Code to delete adjacent column?

[TknExcel]

Windows XP, Excel 2003

Hi,

I have a simple code that looks across the headers in row 5 of my worksheet and deletes any columns with the header "Bad" in it. What I can't figure out is how to make it also delete the adjacent column to the right.

Here is my code so far:

Public Sub DelBadCol()
 
For Each c In ThisWorkbook.Worksheets("Region").Range("5:5")
     If Instr (c, "Bad") Then
          c.EntireColumn.Delete
     End If
Next c
 
End Sub

Any help would be appreciated. Thanks!

 

[VoG]

Try

Public Sub DelBadCol()
 
For Each c In ThisWorkbook.Worksheets("Region").Range("5:5")
     If InStr(c, "Bad") Then
          c.Resize(, 2).EntireColumn.Delete
     End If
Next c
 
End Sub

 

[HalfAce]

Yes, to answer your question directly, Vog has shown the way.
However, because you're deleting these columns, and the direction you're looping from (left to right), if you ever have 3 consecutive columns with 'Bad' in row 5, the third one will get ignored and will not be deleted.
Here's one way to solve that. (another way would be to loop from right to left instead.) But this ought to take care of it as well.

Public Sub DelBadCol()
 
Dim c As Range, DelRng As Range
For Each c In ThisWorkbook.Worksheets("Region").Range("5:5")
  If InStr(c, "Bad") Then
    If DelRng Is Nothing Then
      Set DelRng = c.Resize(, 2).EntireColumn
    Else
      Set DelRng = Union(DelRng, c.Resize(, 2).EntireColumn)
    End If
  End If
Next c
 
If Not DelRng Is Nothing Then DelRng.Delete
 
End Sub

Hope it helps.

 

[TknExcel]

Thanks Peter! I just learned something new. I tested it in my workbook and it did delete both columns, however, it didn't move on to the next "Bad" set of columns. I think it had something to do with the columns shifting to the left after the deletion.

Is there a way to make it look for and delete bad columns from right to left?

 

[VoG]

See HalfAce's suggestion.

 

[TknExcel]

HalfAce, thanks! We apparently posted our replies at the same time. Your code works perfectly.

Thank you both again!

 

[Rick Rothstein]

...and deletes any columns with the header "Bad" in it

HalfAce's follow-up message to Vog's message should give you the approach that will do what you asked for, but I have a different concern about your code. The above quote from your initial message seems to indicate the word "Bad" will be the entire header text for columns so marked. If that is correct, then I don't think you should be using InStr to test for it as that opens you up to bad matches in other columns with text containing the word "Bad" such as "Bad Debts", "Badgers" and the like. Again, if my reading of your set up is correct, why not just test for the word "Bad" directly?

If c.Value = "Bad" Then

 

[TknExcel]

Hi Rick, that is definitely a valid concern. For my purpose, though, the headers in question are titled "Bad audit", "Bad payment", etc.

If one was called "Badgers", I'd probably want to delete it too. lol

 

[Rick Rothstein]

Hi Rick, that is definitely a valid concern. For my purpose, though, the headers in question are titled "Bad audit", "Bad payment", etc.

Okay, good. As I said, I was just checking because your wording made it sound as if the header was only the single word "Bad" (as if you had marked them manually for deletion by changing the header to that word).

If one was called "Badgers", I'd probably want to delete it too. lol

Yes, but would you still want to automatically delete the next column also?