CODEWITHSUNDEEP
vbaexcel
Stu-co
Stu-co

Reputation: 39

VBA Selecting specific rows based on list of relative row numbers and range

So I'm trying to make a sample selector.

The current variables I have are a range that has been specified by the user.

Settings.AmountColumn is a range that looks something along the lines of B1:H100. SampleRefArr is an array of row numbers, relative to the first row of the Settings.AmountColumn.

For example, if my SampleRefArr shows (3,4,7) then I will need to take the following ranges (B4:H4,B5:H5,B8:H8) and copy them to a new spreadsheet. I have built the following code but that does not select the right cells...

counter = UBound(SampleRefArr, 1)

For i = 1 To counter

    rowSelector = SampleRefArr(i) - Settings.AmountColumn.Cells(1, 1).Row + 2
    'Settings.AmountColumn.Cells(rowSelector, 1).EntireRow.Select
    Settings.AmountColumn(Cells(rowSelector, 1), Cells(rowSelector, 10)).Select
Next i

Upvotes: 1

Views: 729

Answers (2)

user3598756
user3598756

Reputation: 29421

you could use this:

Sub RangeSelector(rng As Range, refArr As Variant)
    Intersect(rng, rng.Range("A" & Replace(Join(refArr, ","), ",", ",A")).EntireRow.Offset(1)).Select
End Sub

to be called like:

RangeSelector Range("B1:H100"), Array(3, 4, 7)

which would return cells "B4:H5" and "B8:H8" selected

Upvotes: 1

SJR
SJR

Reputation: 23081

Perhaps this?

counter = UBound(SampleRefArr, 1)

For i = LBound(samplrefarr,1) To counter
    With Settings.AmountColumn
        rowSelector = SampleRefArr(i) - .Cells(1, 1).Row + 2
        'Settings.AmountColumn.Cells(rowSelector, 1).EntireRow.Select
        .Range(.Cells(rowSelector, 1), .Cells(rowSelector, 10)).Select
    End With
Next i

Upvotes: 0

Related Questions