MAcro - Range Selection - Help

[mitko007]

Hi,
i am trying to construct a VBA macro that does the following:

i have a large data set in a column (let say column A) containing clusters of 0's and 1's. I need a way to get the ranges of the clusters of 1's.



1
0



2
0



3
0



4
0



5
1
5


6
1



7
1



8
1
8


9
0



10
0



11
0



12
1
12


13
1



14
1



15
1
15



The table demonstrates what i mean. I need a result that gives something as A5:A8 & A12:15. Another thing that would work fine for me will be the first and last row number of each cluster of ones. I need to crosscheck these rows with other columns afterwards.

I will appreciate you help.

thanks

[Paul_Hossler]

I'm sure there's other (and probably faster) ways to do it, but this is a simple approach




Option Explicit
Sub JustTheOnes()
Dim rData As Range, rOnes As Range, rCell As Range, rArea As Range

Set rData = ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants, xlNumbers)


For Each rCell In rData.Cells
If rCell.Value = 1 Then
If rOnes Is Nothing Then
Set rOnes = rCell
Else
Set rOnes = Union(rOnes, rCell)
End If
End If

Next

For Each rArea In rOnes.Areas
MsgBox rArea.Address
Next
For Each rCell In rOnes.Cells
MsgBox rCell.Address
Next

End Sub