Structuring a Statement and Removing Columns

[audioboxer]

Hello,

I am having an issue with my IF statement below involving removing columns and that it ends up not removing all the columns it should. When I do a debug.print it finds the correct columns to remove however when it actually runs it's not removing them all. I figured out it's because as it deletes them, the columns shift. I cannot figure out how to properly form this to run correctly.

For Each x In Range(Cells(1, 1), Cells(1, lastCol))
match = 0
     For Each y In KeepCols
        If x = y Then
            match = match + 1
        Else
        End If
    Next y
    If match = 0 Then
       'Debug.Print x & " Column Header " & " which is " & x.EntireColumn.Address & " Delete"
       x.EntireColumn.Delete 
    Else
    End If
Next x

 

[Kenneth Hobson]

What is the goal? (1) Remove columns (2) Clear contents.

(1) Loop from last column to first, step -1.

(2):

x.ClearContents

 

[audioboxer]

What is the goal? (1) Remove columns (2) Clear contents.

(1) Loop from last column to first, step -1.

(2):

x.ClearContents

Thanks Kenneth, I am looking to remove the column. It looks like it's on the right track now I didn't think of that however I am unsure about the "step -1" and how to implement it in what I have..

 

[Kenneth Hobson]

I don't know what your match criterion is or would show you.

The default Step value for a For() loop is 1. With cursor in or next to a command work, press F1 for extended help.

My preference when deleting columns or rows is to do it all at once. Then I don't have to Step -1.

e.g. Here, I am removing the column if any value in it is > 2.

Always test code on backup copy. Run this from a Module with blank ActiveSheet.

Sub DelColsMoreThan2_2()
  Dim r As Long, c As Integer
  Dim rr As Range, cc As Range, ur As Range
  
  ActiveSheet.UsedRange.ClearContents
  With Range("A1:D4")
    .Formula = "=row() & "" "" & column()"
    .Value = .Value
  End With
  Range("B3,D4").Value = 2
  
  Set rr = ActiveSheet.UsedRange
  For Each cc In rr
    If IsNumeric(cc.Value) And cc.Value > 1 Then
      If ur Is Nothing Then
        Set ur = cc
        Else: Set ur = Union(ur, cc)
      End If
    End If
  Next cc
  If Not ur Is Nothing Then ur.EntireColumn.Delete
End Sub