vba - 在特定单元格计数

Question

我知道这将是一个简单的调整，但是我尝试了许多变体，并且在谷歌和这个论坛上也没有什么运气。

到目前为止，我的代码为我提供了 B 列的行数，返回值为 225。但是我希望计数从单元格“B17”开始返回值 209。这是因为 B17 是我的电子表格中的表格将包含可变数量的数据。

到目前为止的代码是:

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "B").END(xlUp).row
End With

提前致谢

[

Sub Macro1()

Dim startRow As Double
Dim lastRow As Double
Dim procCol As Double
Dim i As Double

'I will preface this with you should not use activesheet
'you should declare:
'Dim thisWS As Worksheet
'Set thisWS = Thisworkbook.Worksheets("yourworksheetname")
'and use thisWS where you see Activesheet

startRow = ActiveSheet.Range("K16").row
procCol = ActiveSheet.Range("K16").Column

lastRow = ActiveSheet.Cells(Rows.Count, procCol).END(xlUp).row

'for RGB use a tool like
'https://www.w3schools.com/colors/colors_picker.asp


  For i = startRow To lastRow 'this will run through the rows, it's dynamic
   If ActiveSheet.Cells(i, procCol).Value = ActiveSheet.Cells(i + 1, procCol) Then

            Range(ActiveSheet.Cells(i, 2), ActiveSheet.Cells(i, 20)).Interior.Color = RGB(220, 230, 241) 'this is light blue
            Range(ActiveSheet.Cells(i + 1, 2), ActiveSheet.Cells(i + 1, 20)).Interior.Color = RGB(255, 255, 255) 'this is light blue

   Else 'Do nothing

   End If
Next i

End Sub

我唯一改变的是从 B 到 K 的引用单元格，扩展了范围，以便它填充一行而不是单元格并更新颜色？

Answer 1

如果您需要计算 17(包括)和当前单元格之间的行数，这非常简单:

LastRow = Range(ActiveSheet.Range("A17"), Range("A" & ActiveSheet.Rows.Count).End(xlUp)).Rows.Count