CODEWITHSUNDEEP
excelvba
user6796748
user6796748

Reputation:

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

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

Upvotes: 0

Views: 1999

Answers (1)

Lorne
Lorne

Reputation: 181

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.

Upvotes: 1

Related Questions