删除电子表格中<条件>的行当<条件>栏为未知

Question

我需要在电子表格中找到“逾期”和“到期”一词，但它们出现的列将是可变的，记录数（行）也是可变的。我需要删除数据中没有这些值的所有行，然后在删除其他行后汇总工作表上留下的数据。有什么线索吗？

Answer 1

你可以尝试这样的事情。

将其放入宏中运行

Sub Macro1()
Dim sheet As Worksheet
Dim usedRange As Range

    Set sheet = ActiveSheet
    Set usedRange = sheet.usedRange

Dim rowCount As Integer
Dim columnCount As Integer
Dim iRow As Integer
Dim iColumn As Integer

    rowCount = usedRange.Rows.Count
    columnCount = usedRange.Columns.Count

    For iRow = rowCount To 1 Step -1
        For iColumn = 1 To columnCount
            If ((InStr(1, LCase(usedRange(iRow, iColumn)), "overdue") > 0) Or (InStr(1, LCase(usedRange(iRow, iColumn)), "due") > 0)) Then
                usedRange.Range(Cells(iRow, 1), Cells(iRow, columnCount)).Delete
            End If
        Next iColumn
    Next iRow

End Sub

Answer 2

您也可以尝试使用 ADO。

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim strWhere As String
Dim i As Integer

''http://support.microsoft.com/kb/246335

strFile = ActiveWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM [Sheet1$] AS s "

rs.Open strSQL, cn, 3, 3

For i = 0 To rs.fields.Count - 1
    strWhere = strWhere & " AND (UCase(s.[" _
        & rs.fields(i).Name & "] ) Not Like '%DUE%' Or s.[" _
        & rs.fields(i).Name & "] Is Null) "
Next

strSQL = strSQL & " WHERE " & Mid(strWhere, 5)
rs.Close

rs.Open strSQL

For i = 0 To rs.fields.Count - 1
    Sheets("Sheet2").Cells(1, i + 1) = rs.fields(i).Name
Next

Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

Answer 3

Excel 应该能够将除错误之外的任何值强制转换为字符串。因此，如果您的公式返回错误，则可能会导致类型不匹配。这是使用 Find 方法的另一种方法，可以避免该问题。查找可能比循环遍历列慢，但如果您没有大量数据，您将不会注意到它。

Sub DeleteOverDue()

    Dim i As Long
    Dim rFound As Range

    'Loop backward through the used range
    For i = Sheet1.usedRange.Rows.Count To 1 Step -1
        'Should find "due" and "overdue" because of xlPart
        Set rFound = Sheet1.usedRange.Cells(i, 1).EntireRow.Find("due", , xlValues, xlPart)
        'If it's not found, delete the row
        If rFound Is Nothing Then
            Sheet1.usedRange.Cells(i, 1).EntireRow.Delete
        End If
    Next i

End Sub

注意：此代码会删除数据，因此请在您的真实数据副本上使用它，直到您知道它适合您为止。