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 — Patrick Honorez
– Patrick Honorez, Commented Aug 7, 2019 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 stackoverflow.com/a/31816532/380384) — John Alexiou
– John Alexiou, Commented Aug 7, 2019 at 15:35

Lorne · Accepted Answer · 2019-08-07 14:17:32Z

1

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.

edited Aug 7, 2019 at 14:17

answered Aug 7, 2019 at 14:09

Lorne

1813 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tigeravatar Over a year ago

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 = ...

Lorne Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related