Searching for the last non-occurring timing and chart

[xboon_95]

Hi all, I'm trying to search for the last non-occurring timing and chart it. For instance,
00:00:01
00:00:02
00:00:03
.
.
.
.
01:32:20
01:32:21
01:32:22
01:32:23
01:32:24
01:32:25
01:32:26
01:32:26
01:32:26
01:32:26

As you can see, the last timing stops at 01:32:26. I'm only trying to chart from 00:00:01 to 01:32:26. For now, my codes are shown below :

Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("B1", Range("B65536").End(xlUp))
    Set FindRow = SearchRange.Find(" 00:00:01", LookIn:=xlValues, LookAt:=xlWhole)
    
    row_review = FindRow.Row
    
Dim SearchRange1 As Range
    Dim FindRow1 As Range
    Dim YourValue As Range
    Dim TheLastRow As Long
    Set SearchRange1 = Range("B1", Range("B65536").End(xlUp))
[COLOR=#ff0000]    TheLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row[/COLOR]
    
Dim StartVal, endVal
Static SStart, SEnd
Dim Col1


Col1 = "C"


StartVal = Col1 & FindRow.Row
endVal = Col1 & TheLastRow


SStart = "B" & FindRow.Row
SEnd = "B" & TheLastRow


ActiveSheet.Shapes.AddChart.Select
ActiveChart.HasTitle = True
ActiveChart.Parent.Name = "First_Chart1" 'Name Chart
ActiveSheet.Shapes("First_Chart1").IncrementLeft 288 'Move Chart
ActiveChart.ChartType = xlLine
ActiveChart.Legend.Delete


ActiveChart.PlotArea.Select
With Selection.Interior
    .ColorIndex = 15
    .PatternColorIndex = 5
    .Pattern = xlSolid




ActiveChart.ChartArea.Select
With Selection.Interior
    .ColorIndex = 2
    .PatternColorIndex = 5
    .Pattern = xlSolid


ActiveChart.SetSourceData Source:=ActiveSheet.Range(StartVal & ":" & endVal), _
 PlotBy:=xlColumns  ' Series Range
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range(SStart & ":" & SEnd)
'ActiveChart.SeriesCollection(1).Name = "=""Number of Cycles Over Time"""
ActiveChart.SeriesCollection(1).Select


With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent2
    .Weight = 2
    '.ForeColor.TintAndShade = 0
    '.ForeColor.Brightness = 0.435000006
    '.Transparency = 1
ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Text = "Number of Cycles"


ActiveChart.ChartTitle.Text = "Number of Cycles Vs Time"
ActiveChart.Axes(xlCategory).HasTitle = True
ActiveChart.Axes(xlCategory).AxisTitle.Text = "Time (hh:mm:ss)"

The red colored code is the code i use to get the last row of number to chart but it is not reliable as there might be occurring numbers as shown in the example above and my chart will be lengthened with the same occurring timing. Is there any other ways to do this? Any help is appreciated, thank you.

 

[MickG]

Possible this:-

[COLOR="Navy"]Sub[/COLOR] MG10Sep42
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Lstrow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn = Dn.Offset(1) [COLOR="Navy"]Then[/COLOR]
        Lstrow = Dn.Row
            MsgBox Lstrow
            [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[xboon_95]

Possible this:-

[COLOR=Navy]Sub[/COLOR] MG10Sep42
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Lstrow [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Dn = Dn.Offset(1) [COLOR=Navy]Then[/COLOR]
        Lstrow = Dn.Row
            MsgBox Lstrow
            [COLOR=Navy]Exit[/COLOR] For
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Hey, your code is great, but can I search from the bottom to the top instead? There are other recurring data at the top and it sort of interrupted the program.

 

[MickG]

Perhaps this:-

[COLOR="Navy"]Sub[/COLOR] MG11Sep16
[COLOR="Navy"]Dim[/COLOR] Lstrow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 1 [COLOR="Navy"]Step[/COLOR] -1
   [COLOR="Navy"]With[/COLOR] Range("A" & n)
    [COLOR="Navy"]If[/COLOR] .Value = .Offset(1).Value [COLOR="Navy"]Then[/COLOR]
        Lstrow = .Row
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
MsgBox Lstrow
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick