Ranges with Variable start and end based on number of lines of data

[SAMCRO2014]

I feel like I have myself running around in circles. I need a macro that will create a variable range based on the number of rows that contain specific information. The start and end of each range is variable. The columns are not variable.

For example: Employee 123 has 6 rows of data so the range would be B7:B12
Employee 456 has 10 rows of data so the range could be B37:B46
Employee 789 has 2 rows of data so the range could be B99:B100

Any help would be greatly appreciated.

 

[DanteAmor]

Try:

Sub ranges()
    Dim datos As Range, i As Long, n As Long, rng As String
    Set datos = Range("B7", Range("B" & Rows.Count).End(xlUp))
    For i = 1 To datos.Rows.Count
        n = WorksheetFunction.CountIf(datos.Columns(1), datos(i, 1))
        rng = datos(i, 1).Resize(n, 1).Address
        MsgBox "Employee : " & datos(i, 1) & " Range : " & rng
        i = i + n - 1
    Next
End Sub

 

[SAMCRO2014]

You absolutely rock!!!! Thank you!!!!

 

[DanteAmor]

You're welcome, thanks for the feedback.