How to select specific rows from an array containing row Indexes in excel with vba

Question

I am working with a long list in excel and I have to select specific rows that fit criteria. I managed to create an array containing the index number of those rows. All I have to do now is to select those rows.

Here's the code:

Sub Playground()

Dim currentContract As String
currentContract = "none"
Dim CCIsPos As Boolean
CCIsPos = False
Dim asarray() As Integer
Dim i As Integer

ReDim asarray(1 To Sheets("Playground").UsedRange.Rows.Count)
For Each Cell In Sheets("Playground").Range("E:E")

    matchRow = Cell.Row

    If Cell.Value <> currentContract Then
        currentContract = Cell.Value
        If Cells(matchRow, "J") > 0 Then
            CCIsPos = True
        Else
            CCIsPos = False
        End If
       End If
If CCIsPos Then
    i = i + 1
    asarray(i) = matchRow
End If
Next Cell

'Would need a function here selecting rows from the array "asarray"
'Rows(asarray).Select doesn't work.
End Sub

Using the comma "," range operator, you can create a string and use it like this: `range("a1, a4, a6").Entirerow.Select` — iDevlop, Aug 07 '19 at 14:17
If you have more than 16383 rows, using an `Integer` type is a problem. It is recommended to replace all instances of `Integer` with `Long` which is a 32-bit integer value. The `Integer` type is 16-bit wide only. (see https://stackoverflow.com/a/31816532/380384) — John Alexiou, Aug 07 '19 at 15:35

Lorne · Accepted Answer · 2019-08-07T14:17:32.683

I would say you need to make use of the Union() function. Modify you code as below, I am assuming you have checked and confirmed that asarray contains the correct indexes of rows and I will not look into those parts.

Sub Playground()

Dim currentContract As String
currentContract = "none"
Dim CCIsPos As Boolean
CCIsPos = False
Dim i As Integer
Dim selectionRange as Range

For Each Cell In Sheets("Playground").Range("E:E")
    matchRow = Cell.Row
    If Cell.Value <> currentContract Then
        currentContract = Cell.Value
        If Cells(matchRow, "J") > 0 Then
            CCIsPos = True
        Else
            CCIsPos = False
        End If
    End If
    If CCIsPos Then
        If Not selectionRange Is Nothing Then
            Set selectionRange = Union(selectionRange, Sheets("Playground").Rows(matchRow).EntireRow)
        Else
            Set selectionRange = Sheets("Playground").Rows(matchRow).EntireRow
        End If
    End If
Next Cell

selectionRange.Select

End Sub

I hope this resolves your issue.

As written, this code will always error on the first attempt at `Union` because `selectionRange` will be `Nothing` at that time and you can't union a null range. Need to test if `selectionRange` is nothing and if so simply set it to the range that meets the criteria, otherwise Union as shown. You also need to make sure you use the `Set` keyword when assigning object variables (such as ranges), so it should be `Set selectionRange = ...` — tigeravatar, Aug 07 '19 at 14:14
You are right, this also goes to show that I should test my answers before posting them :) I have made the related edits, thank you for the warning. — Lorne, Aug 07 '19 at 14:18

How to select specific rows from an array containing row Indexes in excel with vba

1 Answers1