使用VBA实现Excel批量删除数值低于10的整行（至首个空单元格）

Hey there, let's tackle this problem step by step. You want to remove all rows where the value in Column B is less than 10, stopping at the first empty cell in the data range. Here's a robust VBA script that does exactly that, plus I'll break down how it works so you understand what's going on.

The VBA Code #

Sub DeleteRowsBelowThreshold()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row with data in Column B (stops at first empty cell)
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Loop from bottom to top to avoid skipping rows
    For i = lastRow To 2 Step -1
        ' Check if Column B value is a number and less than 10
        If IsNumeric(ws.Cells(i, "B").Value) And ws.Cells(i, "B").Value < 10 Then
            ws.Rows(i).Delete
        End If
    Next i
    
    MsgBox "Rows with Column B value < 10 have been deleted!", vbInformation
End Sub

Key Details & Explanations #

• Worksheet Setup: Replace "Sheet1" with your actual sheet name (the one containing your data).
• Finding Last Row: End(xlUp) starts from the bottom of Column B and moves up until it hits the first non-empty cell—this ensures we only process your data range and stop at the first empty cell, just like you asked.
• Bottom-to-Top Loop: We loop backwards from the last row to the second row (assuming row 1 is your header) because if we loop top-to-bottom, deleting a row would shift the rows below up, causing us to skip the next row. Looping backwards avoids this issue entirely.
• Numeric Check: The IsNumeric function ensures we don't throw errors if there's non-numeric data in Column B—we only check cells that contain actual numbers.

How to Use This Code #

1. Open your Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the code above into the module.
5. Adjust the sheet name if needed.
6. Press F5 to run the macro, or assign it to a button on your worksheet for easy access.

Example Outcome #

If your original data looks like this:

After running the macro, only the rows for Thomas (18) and John (15) will remain—perfect match for your example!

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