Store row numbers in an array

[Westbury]

I've got 2 types of data and thought it might be a good idea to use an array to store the row number in for subsequent processing; is this a better solution than ifs & loops ?

Both sets of data are in blocks of approx 4 to 7 rows but each block in its raw form is separated by a blank row so they are easily identifiable. The type 1 data I could do with recording the last row of the data set, so blank row -1.

For the type 2 data I need the row number of the first row for each set, so blankrow +1

How do I recall the values from an array for subsequent use?

Thanks for any advice.

Geoff

 

[DanteAmor]

I hope the following helps you.

Sub Store_row_numbers()
  Dim c As Range, ary As New Collection, nRow
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    ary.Add c.Cells(1, 1).Row
  Next
  For Each nRow In ary
    MsgBox "row number: " & nRow
  Next
End Sub

Assuming your data like this:
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:138.77px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >TITLE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Block1 Data1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Block1 Data2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Block1 Data3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Block1 Data4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Block2 Data1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Block2 Data2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Block2 Data3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Block2 Data4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Block2 Data5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Block3 Data1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Block3 Data2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Block3 Data3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Block3 Data4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >Block3 Data5</td></tr></table>

The results will be rows: 2, 7, 13

 

[Westbury]

Thank you. That's great & gives me something to build on.

Geoff

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.