Solved: copying ROW numbers into an array

[cchris_uk]

Hi,
First I must apologise for the subject of this post, I wasn't sure how best to summarise what my question was.

I have a sheet containing many rows of data.
In column A, some of the rows contain a number.

What I want to do is:

1. For i = 1 to (highest value in column A - this will either be stored in a cell somewhere or discovered by this script)
2. FIND i in column A and store the ROW number in an array called list
3. next i

I will then use:

For i = LBound(list) To UBound(list)
MsgBox list(i)
Next i

From a previous thread to display these values.

For example, if the rows contained data such as:

-- A B
01
02 1
03 4
04
05 5
06 2
07
08 3

Then the end result of the script will be equivilent of entering:
list = array(02, 06, 08, 03, 05)
into my subroutine.

Thanks for all the help, I am slowly removing as much manual input as possible from my spreadsheets to minimise human error!


Chris

[Bob Phillips]

Public Sub BuidArray()
Dim mpRows As Variant
Dim mpCell As Range
Dim mpCount As Long
Dim mpNext As Long
Dim i As Long

With ActiveSheet

mpCount = Application.Count(.Columns(1))
ReDim mpRows(1 To mpCount)

mpNext = 1
For i = 1 To Application.Max(.Columns(1))

Set mpCell = Nothing
Set mpCell = .Columns(1).Find(i, lookat:=xlWhole)
If Not mpCell Is Nothing Then

mpRows(mpNext) = mpCell.Row
mpNext = mpNext + 1
End If
Next i
End With
End Sub

[cchris_uk]

Thank you,
This does exactly what it says on the tin!

Chris