VBA Rows.Count选择 [英] VBA Rows.Count in Selection

问题描述

我正在寻找一个用户选择显示在行动按钮旁边的页面顶部的行数，即，按钮说生成电子邮件，旁边说选择了x项目。

I'm looking to work out how many rows a user has selected to be displayed at the top of the sheet next to an action button, I.e. Button says "Generate Email" and next to it says "x items selected".

由于每次更改选择时都会更新，因此我有以下代码：

As this is updated everytime the selection is changed, I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Sheet1.Range("E1") = Target.Rows.Count & " items selected"
End Sub

如果用户选择连续的行，例如7:10返回4.

This works fine if the user selects consecutive rows, for e.g. 7:10 returns 4.

我的问题是如果用户选择了第7行和第10行。它只会返回1（选择的第一部分中的行） 。

My problem is if a user selected rows 7, and 10. It would only return 1 (the rows in the first part of the selection).

从我发现的，没有办法从属性中获取这个值，但是我不知道如何遍历所有的部分的选择/目标，并计算行总和。那么也有可能用户选择说A7，C7和A10。 A7和C7与同一个项目有关，所以这只应该被视为一个而不是两个，我认为我的假设代码将会执行...

From what I've found, there is no way of just getting this value from a property, but I can't get my head around how to iterate through all parts of the selection/target and calculate the sum of rows. Then there is also the possibility that the user selects say A7, C7, and A10. A7 and C7 relate to the same item, so this should only really be treated as one, not two, which I think my hypothetical code would do...

有没有人试图实现这一点，并成功，或者可能指向我可以帮助的一些属性的方向？我尝试了一个单独的函数来实现它，但是这不起作用。

Has anyone tried to achieve this before and been successful or could point me in the direction of some properties which may help? I tried a separate function to achieve it, but that wasn't working either.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Sheet1.Range("E1") = getRowCount(Target) & " items selected"
End Sub

Function getRowCount(selectedRanges As Ranges)
  rowCount = 0
  For Each subRange In selectedRanges
    rowCount = rowCount + subRange.Rows.Count
  Next

  getRowCount = rowCount
End Function

推荐答案

我认为这样会奏效。 （当我尝试的时候）。

I think this will work. (Did when I tried it.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Create a range containing just column A
    Dim subRange As Range
    Dim r As Range
    For Each subRange In Target.Areas
        If r Is Nothing Then
            Set r = subRange.EntireRow.Columns(1)
        Else
            Set r = Union(r, subRange.EntireRow.Columns(1))
        End If
    Next
    'Count how many cells in the combined column A range
    Sheet1.Range("E1") = r.Cells.Count & " items selected"
End Sub

这篇关于VBA Rows.Count选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！