# Formula to get set of rows numbers where match criteria

#### Fractalis

##### Active Member
Hello to all,

I have this table in range A1:C7:

 Header1 Header2 Header3 98 3 462 77 2 77 65 8 23 65 2 982 2 5 11 44 2 35

<tbody>
</tbody>

- A formula that gives me a set of row numbers of all occurences of 2 in column B, result should be {3,5,7}
- and how to store that result in an VBA array to use it in a macro.

In this example, there are 3 occurences, so, the set result has 3 elements. But if there are
50 occurences, the result set should have 50 elements.

I hope is possible.

Thanks in advance for any help.

Last edited:

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi,

Is this what you are looking for? This will loop through looking at the data on sheet 1, and any row in column B that it finds a 2 it will copy the row number onto Sheet 2. The loop works by counting how many rows there are, so this should solve the issue as to sometimes there are 3, whilst other times there are 50 etc.

Regards,
Chris

Sub Findrows()
Dim RC As Integer
Dim RL As Integer
Dim RS As Integer
RC = Sheets("Sheet1").UsedRange.Count
RS = 0
For RL = 1 To RC
Sheets("Sheet1").Cells(RL,2).Select
If ActiveCell.Value = 2 Then
RS = RS + 1
Sheets("Sheet2").Cells(RS, 1).Value = RL
Else
End If
Next RL
End Sub

Try:

Code:
``````Sub Test()
Dim Cell As Range
Dim i As Long
Dim Arr() As Long
For Each Cell In Range("B2:B7")
With Cell
If .Value = 2 Then
i = i + 1
ReDim Preserve Arr(1 To i)
Arr(i) = .Row
End If
End With
Next Cell
End Sub``````

Hello Chris and Andrew,

Many thanks for your help, your codes do what I need without using a spreedsheet formula.

Thank you again.

Regards

Hi Fractalis,

That's great. Thanks for the feedback.

Regards,
Chris

Replies
19
Views
420
Replies
20
Views
948
Replies
1
Views
82
Replies
4
Views
192
Replies
4
Views
318

1,196,414
Messages
6,015,123
Members
441,873
Latest member
saustark

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back