月份: 2024 年 3 月

Sub DeleteRowsBasedOnCriteria()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim deleteFlag As Boolean
    Dim concatenateString As String

    ' Set the active sheet as the worksheet
    Set ws = ActiveSheet

    ' Find the last row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' First pass: Mark rows for deletion based on the first criteria
    For i = lastRow To 1 Step -1
        ' Initialize deleteFlag to False for each row
        deleteFlag = False

        ' Check if any of the columns A through H is empty
        For j = 1 To 8 ' Columns A through H
            If ws.Cells(i, j).Value = "" Then
                ' Set the deleteFlag to True if any column is empty
                deleteFlag = True
                Exit For
            End If
        Next j

        

        ' Delete the entire row if any column A through H is empty or if the concatenation matches
        If deleteFlag Then
            ws.Rows(i).Delete
        End If
    Next i

    ' Find the last row again after the first deletion
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Second pass: Delete rows based on the second criteria
    For i = lastRow To 1 Step -1
        ' Check if the concatenation of the first 9 columns equals a specific string
        concatenateString = ""
        For j = 1 To 9 ' Columns A through I
            concatenateString = concatenateString & ws.Cells(i, j).Value
        Next j

        ' Delete the entire row if the concatenation matches the specific string
        If concatenateString = "訂單號碼訂單日預定出港日品名台灣品名數量/KGS包裝方式目的地備註" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub