Selection.rows.count only returns 1 row instead of 3

Question

I am fairly new to VBA.

VBA selectes 3 rows, but only count 1? I could not find a similar case, which I could understand.

I could not paste the whole code, as stackoverflow does not allow that kind of post.

Code underneath.


For i = 39 To lastRow

    ' Look for identical rows as input
    If Cells(i, 3) = Range("C29") And Cells(i, 4) = Range("D29") And Cells(i, 5) = Range("E29") And Cells(i, 6) = Range("F29") And Cells(i, 7) = Range("G29") And Range("H29") = Cells(i, 8) Then
        If foundRange Is Nothing Then

                'Defines the first row as foundrange, if nothing found earlier
                Set foundRange = Range(Cells(i, 3), Cells(i, 8))
        Else

            'Adds rows to foundRange, if one (or more) rows are found
            Set foundRange = Union(foundRange, Cells(i, 3), Cells(i, 4), Cells(i, 5), Cells(i, 6), Cells(i, 7), Cells(i, 8))
    End If
End If
Next i


If foundRange Is Nothing Then
    MsgBox "No rows found acording to input"
Else

'Count amount of rows to be removed
foundRange.Rows.Select
MsgBox selection.Rows.Count

Delete: `foundRange.Rows.Select`and change `MsgBox selection.Rows.Count` for `MsgBox foundRange.Rows.Count`. Also take a look to this [post](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) on how to avoid using select in VBA — Damian, Oct 23 '19 at 07:18
Note: This is a very complicated (and expensive) way to check and count some data. — JvdV, Oct 23 '19 at 07:18
I think if the rows are not contiguous it will only pick up the first `Area`. A workaround would be `foundRange.count/6` or loop through the areas. — SJR, Oct 23 '19 at 07:19
On the subject of efficiency: `Set foundRange = Union(foundRange, Cells(i, 3), Cells(i, 4), Cells(i, 5), Cells(i, 6), Cells(i, 7), Cells(i, 8))` is the same as just writing `Set foundRange = Union(foundRange, Range(Cells(i, 3), Cells(i, 8)))`. That said, this is basically an overcomplicated `WorksheetFunction.CountIfs` — Chronocidal, Oct 23 '19 at 07:30
Should i loop though the data instead? I use the code with a button, so people can add lines to a table without making duplicates. But i will check out the post you have linked to Damian — Jan Ettema, Oct 23 '19 at 07:31
@JanEttema do you need this range for anything on your code later on? If not, as Chronocidal said, you could just use a `COUNTIFS` — Damian, Oct 23 '19 at 07:32
But the range is used later on in the code, as i need to remove the required rows by the user — Jan Ettema, Oct 23 '19 at 07:37
Then I'd use a `Dim counter As Long` and when the criteria is meet, `counter = counter + 1` So you can get the number of rows and retain your ranges. @JanEttema — Damian, Oct 23 '19 at 07:41
Thank you all for your input! I have some to read up about now. — Jan Ettema, Oct 23 '19 at 07:48

score 2 · Accepted Answer · answered Oct 23 '19 at 07:43

If you don't need the cells to be Selected, or stored in foundRange to use later on, then you can just use a CountIfs, like so:

With ActiveSheet.Range(ActiveSheet.Cells(39, 3), ActiveSheet.Cells(lastRow, 8))
    MsgBox WorksheetFunction.CountIfs(.Columns(1), Range("C29").Value, _
        .Columns(2), Range("D29").Value, _
        .Columns(3), Range("E29").Value, _
        .Columns(4), Range("F29").Value, _
        .Columns(5), Range("G29").Value, _
        .Columns(6), Range("H29").Value)
End With

If you do need to have the cells stored in a range, then you may need to look through Areas of the Range:

'Most of your code here...

If foundRange Is Nothing Then
    MsgBox "No rows found acording to input"
Else
    'Count amount of rows to be removed
    foundRange.Rows.Select 'Is this line necessary?

    Dim RangeArea As Range, TotalRows AS Long: TotalRows = 0

'N.B. If your Areas can be in different Columns of the same Row:
'     use foundRange.EntireRow.Areas to prevent double-counting!
    For Each RangeArea In foundRange.Areas
         TotalRows = TotalRows + RangeArea.Rows.Count
    Next RangeArea

    MsgBox TotalRows
End If

Selection.rows.count only returns 1 row instead of 3

1 Answers1