如果不喜欢，则删除列

Question

我在一个单元格中有几个值，命名为'pidlist'，因此：

'I.A.3', 'I.A.4', 'I.O.9', 'I.U.3', 'I.U.4', 'I.U.6', 'O.D.1', 'O.D.2'

我正在尝试循环遍历另一张表中的列，如果标题不在上面的列表中，请删除列。

'Find the last used column in a Row: row 1 in this example
Dim LastCol As Integer
With ActiveSheet
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
Dim lColumn As Long
Dim iCntr As Long
lColumn = LastCol
For iCntr = lColumn To 1 Step -1
    If Not Cells(1, iCntr).Value Like "*" & PIDList & "*" And Cells(1, iCntr).Value <> "Delta" And Not Cells(1, iCntr).Value <> "CloseBal" _
        And Not Cells(1, iCntr).Value <> "Totals" And Not Cells(1, iCntr).Value <> "DESC1" And Not Cells(1, iCntr).Value <> "AsOfDate" Then
        Columns(iCntr).Delete
    End If
    Debug.Print Cells(1, iCntr).Value
Next

在列中，我有这样的数据：

I.A.3   I.A.4   I.O.9   I.U.3   I.U.4   I.U.6   O.D.1   O.D.2   O.D.3   O.D.4   O.D.5   O.D.6

如果不...喜欢，它不会删除任何列。我尝试过有没有引号；相同的结果。知道这里怎么了？

Answer 1

据我了解，您的条件子句是表格

if (A) and (B) and (C) and (D) then

如果任何a，b，c或d是false，则未执行删除。

您的某些条件是形式

And Not Cells(1, iCntr).Value <> "CloseBal"
And Not Cells(1, iCntr).Value <> "Totals"

可以翻译为

And Cells(1, iCntr).Value = "CloseBal"
And Cells(1, iCntr).Value = "Totals"

这些条件之一肯定是错误的。

因此，我认为整个条款是错误的，这意味着不会删除列。是不是吗？

Answer 2

稍有不同的方法将是类似的，这将删除与数组中不匹配值的任何列：

Sub foo()
Dim iCntr As Long
Dim val As String
Dim PIDList() As Variant
Dim LastCol As Long
PIDList = Array("I.A.3", "I.A.4", "I.O.9", "I.U.3", "I.U.4", "I.U.6", "O.D.1", "O.D.2", "Delta", "CloseBal", "Totals", "DESC1", "AsOfDate") 'add all values to keep to this array
LastCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
For iCntr = LastCol To 1 Step -1
    val = "Not Found" 'set variable as not found
    currentcol = ActiveSheet.Cells(1, iCntr).Value 'get the value of the column header for comparison
    For x = LBound(PIDList) To UBound(PIDList)
    If currentcol = PIDList(x) Then 'loop through array to see if match is found
        val = "Found"
    End If
    Next x
    If val <> "Found" Then 'if match is not found then delete
        ActiveSheet.Columns(iCntr).EntireColumn.Delete
        val = "" 'reset variable for next loop
    End If
Next iCntr
End Sub

Answer 3

首先，让我们澄清您想要的东西。据我了解，如果不包含任何列出的标头，则要删除列。

具有"*"和PIDList和CloseBal和Totals作为一列的标头是不合格的。因此，在IF语句中使用"AND"使得false子句。尝试使用"OR"。

另外，尝试向后尝试：

If header = "*" OR header = PIDList OR header = CloseBal OR header = Totals
then print "header match"
else
delete column
End if

*具有NOT和'<>'的意思是NOT NOTEQUAL TO，即'IS EQUAL TO'。这使得您的子句反之亦然。

Answer 4

好吧，我想你必须用2个语言来做：

    If Not PIDList Like "*" & Cells(1, iCntr).Value & "*" Then
        If Cells(1, iCntr).Value <> "Delta" And _
            Cells(1, iCntr).Value <> "CloseBal" And _
            Cells(1, iCntr).Value <> "Totals" And _
            Cells(1, iCntr).Value <> "DESC1" And _
            Cells(1, iCntr).Value <> "ASOFDATE" Then
                Columns(iCntr).Delete
        End If
    End If
Next

对我有用。