Excel VBA从指定单元格(B17)统计行数及宏代码调整咨询

1. Adjusting Row Count to Start at B17 #

Your original code correctly finds the last filled row in column B, but to get the number of rows starting from B17 (instead of counting from row 1), you just need to subtract the starting row offset. Here's how to modify it:

With ActiveSheet
    Dim lastRowB As Long
    lastRowB = .Cells(.Rows.Count, "B").End(xlUp).Row
    Dim rowCountFromB17 As Long
    rowCountFromB17 = lastRowB - 16 ' B17 is row 17, so subtract 16 to get the count from 17 onwards
    ' When lastRowB is 225, rowCountFromB17 will be 209 as you need
End With

This calculates the total number of rows between B17 and the last filled cell in column B directly.

2. Review and Correction of Macro1 #

Your code has solid core logic, but there are a few tweaks to make it more robust and correct:

Key Issues to Fix: #

• Incorrect variable type: Using Double for row/column numbers is unnecessary (they’re integers). Switch to Long to avoid precision errors with large row counts.
• Loop out of bounds: When i reaches lastRow, i+1 will reference a cell beyond your data range, leading to unintended comparisons with empty cells. Adjust the loop to run up to lastRow - 1.
• Comment typo: The second color is white, but your comment incorrectly labels it as "light blue".
• ActiveSheet dependency: As your own comment suggests, replacing ActiveSheet with a specific worksheet reference prevents errors if the wrong sheet is active.

Corrected Macro1 Code: #

Sub Macro1()
    Dim startRow As Long
    Dim lastRow As Long
    Dim procCol As Long
    Dim i As Long
    
    ' Replace "YourSheetName" with the actual name of your worksheet
    Dim thisWS As Worksheet
    Set thisWS = ThisWorkbook.Worksheets("YourSheetName")
    
    startRow = thisWS.Range("K16").Row
    procCol = thisWS.Range("K16").Column
    lastRow = thisWS.Cells(thisWS.Rows.Count, procCol).End(xlUp).Row
    
    ' Loop only up to the second-to-last row to avoid i+1 going out of bounds
    For i = startRow To lastRow - 1
        If thisWS.Cells(i, procCol).Value = thisWS.Cells(i + 1, procCol).Value Then
            ' Color current row (columns 2 to 20) light blue
            thisWS.Range(thisWS.Cells(i, 2), thisWS.Cells(i, 20)).Interior.Color = RGB(220, 230, 241)
            ' Color next row white
            thisWS.Range(thisWS.Cells(i + 1, 2), thisWS.Cells(i + 1, 20)).Interior.Color = RGB(255, 255, 255)
        End If
    Next i
End Sub

Optional Cleanup with With Block: #

For even more readable code, wrap the worksheet in a With block:

Sub Macro1()
    Dim startRow As Long
    Dim lastRow As Long
    Dim procCol As Long
    Dim i As Long
    
    ' Replace "YourSheetName" with your worksheet's name
    With ThisWorkbook.Worksheets("YourSheetName")
        startRow = .Range("K16").Row
        procCol = .Range("K16").Column
        lastRow = .Cells(.Rows.Count, procCol).End(xlUp).Row
        
        For i = startRow To lastRow - 1
            If .Cells(i, procCol).Value = .Cells(i + 1, procCol).Value Then
                .Range(.Cells(i, 2), .Cells(i, 20)).Interior.Color = RGB(220, 230, 241)
                .Range(.Cells(i + 1, 2), .Cells(i + 1, 20)).Interior.Color = RGB(255, 255, 255)
            End If
        Next i
    End With
End Sub

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