How to count the number of rows between two values

[Kishan]

Hi,

Column R there is numbers with gaps (only 1 to 9), I want to count number of rows (in every intervals) between values 1 to 9 and result in range U6 down and in last interval count rows “1” to data end of the column Q
Example...

Book1
	Q	R	S	T	U
1
2
3
4					Rows Count
5					1 to 9
6	1	1		1st Time	26
7	2	2		2nd Time	19
8	3			3rd Time
9	4	3
10	5	4
11	6	5
12	7
13	8	6
14	9
15	10
16	11
17	12
18	13
19	14
20	15
21	16
22	17
23	18
24	19
25	20	7
26	21
27	22
28	23
29	24	8
30	25
31	26	9
32	27	1
33	28	2
34	29
35	30
36	31
37	32
38	33	3
39	34	4
40	35	5
41	36	6
42	37	7
43	38
44	39
45	40
46	41
47	42
48	43
49	44
50	45
Sheet1

VBA solution if possible

Thank you in advance
Kishan

 

[JLGWhiz]

Try this

Sub t()
Dim fn As Range, lr As Long, a As Long
With ActiveSheet
    lr = .Cells(Rows.Count, "R").End(xlUp).Row
    a = 6
    Set fn = .Range("R6:R" & lr).Find("9", , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fAdr = fn.Address
            Do
                If .Range("U6") = "" Then
                    .Range("U6") = (fn.Row - a) + 1
                Else
                    .Cells(Rows.Count, "U").End(xlUp)(2) = (fn.Row - a) + 1
                End If
                a = fn.Row + 1
                Set fn = .Range("R6:R" & lr).FindNext(fn)
                If fn.Address = fAdr Then
                    .Cells(Rows.Count, "U").End(xlUp)(2) = (lr - a) + 1
                End If
            Loop While fn.Address <> fAdr
        End If
End With
End Sub

 

[Kishan]

Try this

Thank you JLGWhiz, almost working perfect working only as I request last segment result could be as long as data find in column Q, I explain you why macro is resulting U6 = 26 and U7 = 11 (what if 8 does not appear in next x rows I always will get result U = 11 but really current time lap is 19 not 11 so is it possibility to get final segment result U7 = 19 instead 11

Regards,
Kishan

 

[JLGWhiz]

Thank you JLGWhiz, almost working perfect working only as I request last segment result could be as long as data find in column Q, I explain you why macro is resulting U6 = 26 and U7 = 11 (what if 8 does not appear in next x rows I always will get result U = 11 but really current time lap is 19 not 11 so is it possibility to get final segment result U7 = 19 instead 11

Regards,
Kishan

Sorry, I based the last row on Column R, because that was the one that held the series 1 to 9. I have changed the last row reference to column Q. See if this works better.

Sub t2()
Dim fn As Range, lr As Long, a As Long
With ActiveSheet
    lr = .Cells(Rows.Count, "Q").End(xlUp).Row
    a = 6
    Set fn = .Range("R6:R" & lr).Find("9", , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fAdr = fn.Address
            Do
                If .Range("U6") = "" Then
                    .Range("U6") = (fn.Row - a) + 1
                Else
                    .Cells(Rows.Count, "U").End(xlUp)(2) = (fn.Row - a) + 1
                End If
                a = fn.Row + 1
                Set fn = .Range("R6:R" & lr).FindNext(fn)
                If fn.Address = fAdr Then
                    .Cells(Rows.Count, "U").End(xlUp)(2) = (lr - a) + 1
                End If
            Loop While fn.Address <> fAdr
        End If
End With
End Sub

 

[Kishan]

See if this works better.

Sweet! JLGWhiz, works the best

Thank for your time and help

Regards,
Kishan

 

[JLGWhiz]

Sweet! JLGWhiz, works the best

Thank for your time and help

Regards,
Kishan

You're welcome,
Regards, JLG

 

[dolma]

This is amazing - I was looking for something almost identical to this, and I have been able to manipulate it ALMOST to what I need.

Would anyone be able to help me put this through a loop, so that assuming everything is identical to above, but then there are multiple columns of data (ie, column R repeats to column S, T, U, V... etc - for a lot of columns), that the loop does the above macro, then moves on to the next column, then the next, then the next etc.

I have moved the results of the data from Cell U6 to a separate tab, but would like the results from Column R to update in Column R on a new tab, then the results from column S to update column S in a separate tab, and so on.

Hopefully that makes sense! Thanks in advance