删除没有特定字符串的列

I'm working on a data clean up project in Excel and working to build a macro that cleans up and deletes out unnecessary columns in a report.

我正在使用Excel中的数据清理项目,并正在构建一个宏来清理和删除报表中不必要的列。

I'm having issues looping through the columns to delete columns that don't have certain header text (I have 3 separate strings that I am looking to find and not delete).

我有问题循环遍历列以删除没有特定标题文本的列(我有3个单独的字符串,我希望找到而不是删除)。

I found a previous question that helped me get started Here.

我找到了一个先前的问题,帮助我在这里开始。

How would I go about expanding this code to take into account multiple strings?

我将如何扩展此代码以考虑多个字符串?

The code I attempted is below.

我尝试的代码如下。

Dim currentColumn As Integer
Dim columnHeading As String

For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1

    columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value

    'CHECK WHETHER TO KEEP THE COLUMN
    Select Case columnHeading
        Case "Account Name", "Account ID", "Contract ID", "Delivery Date"
            'Do nothing
        Case Else
            'Delete if the cell doesn't contain "string-"
            If InStr(1, _
               ActiveSheet.UsedRange.Cells(1, currentColumn).Value, "string1-" Or "string2-" Or "string3-", vbBinaryCompare) = 0 Then

                ActiveSheet.Columns(currentColumn).Delete

            End If

    End Select
Next

Thanks!

1 个解决方案

#1

You can't embed an Or statement in InStr like that - you'll have to use multiple InStrs to test against your columns. Also, since you're deleting columns that contain the string, you need to check if InStr returns a value greater than zero, not equal to:

您无法在InStr中嵌入Or语句 - 您必须使用多个InStrs来测试您的列。此外,由于您要删除包含该字符串的列,因此需要检查InStr是否返回大于零的值,不等于:

Sub Test()

Dim currentColumn As Integer
Dim columnHeading As String

For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1

    columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value

    'CHECK WHETHER TO KEEP THE COLUMN
    Select Case columnHeading
        Case "Account Name", "Account ID", "Contract ID", "Delivery Date"
            'Do nothing
        Case Else
            'Delete if the cell doesn't contain "string-"
            If InStr(columnHeading, "string1-") = 0 And _
               InStr(columnHeading, "string2-") = 0 And _
               InStr(columnHeading, "string3-") = 0 Then

                ActiveSheet.Columns(currentColumn).Delete

            End If

    End Select
Next

End Sub