使用For-Next双重循环删除数据时出现全量删除报错的问题

Hey there, let's figure out why your code is deleting everything and fix it properly—especially since you're dealing with 100k rows, efficiency matters a lot!

What's Wrong With Your Current Code? #

• Incorrect Match Logic: Your inner loop uses InStr for a partial match, so if a URL contains your target app URL as a substring (e.g., web.example.com/app.foo.com), it'll get deleted by mistake. You need an exact match for the URL in column 3.
• Stale Range Reference: You define rng = ActiveSheet.UsedRange once at the start, but when you delete rows, this range doesn't update. The loop keeps referencing the original cell count, which leads to invalid cell references and unintended deletions.
• Nested Loop Inefficiency: A double loop over 100k rows is extremely slow, and deleting rows one by one forces Excel to recalculate the sheet every time—wasting tons of time.

The Fix: Efficient URL Collection & Batch Deletion #

Here's a better approach that's fast, accurate, and avoids accidental mass deletions:

1. Collect all unique app-associated URLs first: Scan the sheet once to gather every URL linked to the app (no duplicates).
2. Mark rows for deletion: Scan again to find all rows where column 3 matches any of those app URLs.
3. Delete all marked rows in one go: Batch deletion is way faster than deleting rows individually, especially for large datasets.

Working VBA Code

Sub DeleteAppDependantRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim appUrls As Collection
    Dim i As Long
    Dim deleteRows As Range
    Dim currentUrl As String
    
    ' Speed up execution by disabling screen updates/events
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row ' Get last row with data in URL column (3)
    Set appUrls = New Collection
    
    ' Step 1: Gather unique URLs tied to app usage
    On Error Resume Next ' Skip duplicate URLs in the collection
    For i = 1 To lastRow
        ' Check if any cell in the row indicates app usage (adjust to a specific column if needed!)
        If InStr(LCase(ws.Rows(i).Value), "app.") > 0 Then
            currentUrl = LCase(ws.Cells(i, 3).Value)
            appUrls.Add currentUrl, Key:=currentUrl ' Add only unique URLs
        End If
    Next i
    On Error GoTo 0 ' Reset error handling
    
    ' Step 2: Collect all rows to delete
    For i = 1 To lastRow
        currentUrl = LCase(ws.Cells(i, 3).Value)
        ' Check if current URL is in our app URL list
        On Error Resume Next
        appUrls.Item currentUrl
        If Err.Number = 0 Then ' URL found in app list
            If deleteRows Is Nothing Then
                Set deleteRows = ws.Rows(i)
            Else
                Set deleteRows = Union(deleteRows, ws.Rows(i))
            End If
        End If
        On Error GoTo 0
    Next i
    
    ' Step 3: Delete all marked rows (if any exist)
    If Not deleteRows Is Nothing Then
        deleteRows.Delete
        MsgBox "Deleted " & deleteRows.Count & " rows associated with app URLs."
    Else
        MsgBox "No app-associated URLs found."
    End If
    
    ' Restore screen updates/events
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Key Improvements: #

• Exact URL Matching: Uses a collection to store unique app URLs, ensuring we only delete rows with exact matches (case-insensitive).
• Batch Deletion: Collects all rows to delete first, then removes them in one operation—this cuts down on Excel's recalculation time drastically for large datasets.
• Error Handling: Ignores duplicate URLs in the collection so we don't process the same URL multiple times.
• Performance Optimizations: Disables screen updates and events to speed up execution, which is critical for 100k rows.

Quick Notes: #

• If your "app usage marker" is in a specific column (not any cell in the row), adjust the first loop to check only that column (e.g., If InStr(LCase(ws.Cells(i, 2).Value), "app.") > 0 if column 2 marks app usage).
• Always test this on a copy of your data first—deleting rows can't be undone unless you have a backup!

内容的提问来源于stack exchange，提问作者TheOtter