Excel VBA列计数问题：遇空单元格停止计数且覆盖内容求助

I see two key issues with your current code that are causing the problems you described, and I've fixed them below:

Corrected Code #

Public Sub Test()
    Dim count_of_Dog As Long
    Dim count_of_Cat As Long
    Dim count_of_others As Long
    Dim count_of_all As Long
    Dim items As Variant
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim LastRow As Long
    
    ' Set the worksheet to use (change to your sheet name if needed)
    Set ws = ActiveSheet
    
    ' Find the LAST row with ANY data in column N (ignores empty cells in between)
    Set lastCell = ws.Cells(ws.Rows.Count, "N").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If lastCell Is Nothing Then
        MsgBox "No data found in column N!", vbExclamation
        Exit Sub
    End If
    LastRow = lastCell.Row
    
    ' Initialize counters
    count_of_Dog = 0
    count_of_Cat = 0
    count_of_others = 0
    count_of_all = 0
    
    ' Loop through all rows from 1 to the last row with data
    For i = 1 To LastRow
        items = ws.Range("N" & i).Value
        
        ' Use vbTextCompare to make the search case-insensitive (matches Dog, DOG, etc.)
        If InStr(1, items, "dog", vbTextCompare) > 0 Then
            count_of_Dog = count_of_Dog + 1
        ElseIf InStr(1, items, "cat", vbTextCompare) > 0 Then
            count_of_Cat = count_of_Cat + 1
        ElseIf items <> "" Then ' Only count non-empty cells as "Others"
            count_of_others = count_of_others + 1
        End If
    Next i
    
    ' Calculate total count
    count_of_all = count_of_Dog + count_of_Cat + count_of_others
    
    ' Write results 3 rows below the LAST data row (so no overwriting existing content)
    With ws
        .Range("N" & LastRow).Offset(3, 0).Value = "Count"
        .Range("N" & LastRow).Offset(4, 0).Value = count_of_Dog
        .Range("N" & LastRow).Offset(5, 0).Value = count_of_Cat
        .Range("N" & LastRow).Offset(6, 0).Value = count_of_others
        .Range("N" & LastRow).Offset(7, 0).Value = count_of_all
        
        .Range("N" & LastRow).Offset(3, 1).Value = "Keywords"
        .Range("N" & LastRow).Offset(4, 1).Value = "DOGS"
        .Range("N" & LastRow).Offset(5, 1).Value = "CATS"
        .Range("N" & LastRow).Offset(6, 1).Value = "Others"
        .Range("N" & LastRow).Offset(7, 1).Value = "Total"
    End With
End Sub

Key Changes Made: #

1. Accurate LastRow Calculation:

   • Replaced End(xlUp) with Find to locate the last row with any data in column N, even if there are empty cells in between. This ensures we count every non-empty cell in the column, not just the top contiguous block.
   • Added a check to handle cases where column N is completely empty (shows a message and exits the sub).

2. Case-Insensitive Search:

   • Modified InStr to use vbTextCompare, so it counts "Dog", "DOG", "Cat", "CAT", etc., instead of only lowercase matches. Remove this parameter if you want strict case sensitivity.

3. Cleaner Code Structure:

   • Removed unused row_number variable.
   • Used With ws to simplify writing results to the worksheet.
   • Added comments to make the code easier to follow.

4. Safe Result Placement:

   • Results are now written 3 rows below the actual last data row (not the truncated contiguous row), so they'll never overwrite existing content in column N.

This should resolve both issues: your counts will include all non-empty cells in column N, and the results will be placed safely below all existing data.

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