Restart any time range back to zero

energistics

New Member
Joined
Feb 11, 2016
Messages
30
Hi guys,


Realy appreciate your assistance with this one...
I randomly pick data of seconds and have to convert that range to start at zero seconds and continue through out the
range thus crateing a time lapse between each cell in the range

i.e.
This is the random data in range B3:B12


232.567
242.567
252.568
262.568
272.569
282.569
292.57
302.57
312.57


Heare is my macro that creates a new column next to it and runs the math convert time like this =(B3-$B$3)/60
so as you can see the range is hard coded to max of C3:C900... and sometimes my range can be much smaller than that
as you can see in the range selection example above.... so here is the question how do I make is smart enough to know
the selection I've dumped in cell B3:B? (? means any range) has reached the last one to convert and stop there?
This macro was done through the recorder button. If there is a better way to do this, please help.


Code:
Sub Time_Convert_Min()
'
' Time_Convert_Min Macro
'


'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=(RC[-1]-R3C2)/60"
    Range("C3").Select
    Selection.AutoFill Destination:=Range("C3:C900"), Type:=xlFillDefault
    Range("C3:C900").Select
    ActiveWindow.ScrollRow = 865
    ActiveWindow.ScrollRow = 862
    ActiveWindow.ScrollRow = 858
    ActiveWindow.ScrollRow = 853
    ActiveWindow.ScrollRow = 846
    ActiveWindow.ScrollRow = 840
    ActiveWindow.ScrollRow = 828
    ActiveWindow.ScrollRow = 821
    ActiveWindow.ScrollRow = 812
    ActiveWindow.ScrollRow = 800
    ActiveWindow.ScrollRow = 791
    ActiveWindow.ScrollRow = 778
    ActiveWindow.ScrollRow = 767
    ActiveWindow.ScrollRow = 757
    ActiveWindow.ScrollRow = 746
    ActiveWindow.ScrollRow = 736
    ActiveWindow.ScrollRow = 725
    ActiveWindow.ScrollRow = 716
    ActiveWindow.ScrollRow = 704
    ActiveWindow.ScrollRow = 694
    ActiveWindow.ScrollRow = 679
    ActiveWindow.ScrollRow = 667
    ActiveWindow.ScrollRow = 654
    ActiveWindow.ScrollRow = 640
    ActiveWindow.ScrollRow = 627
    ActiveWindow.ScrollRow = 612
    ActiveWindow.ScrollRow = 600
    ActiveWindow.ScrollRow = 586
    ActiveWindow.ScrollRow = 573
    ActiveWindow.ScrollRow = 559
    ActiveWindow.ScrollRow = 545
    ActiveWindow.ScrollRow = 530
    ActiveWindow.ScrollRow = 516
    ActiveWindow.ScrollRow = 500
    ActiveWindow.ScrollRow = 486
    ActiveWindow.ScrollRow = 469
    ActiveWindow.ScrollRow = 453
    ActiveWindow.ScrollRow = 438
    ActiveWindow.ScrollRow = 424
    ActiveWindow.ScrollRow = 409
    ActiveWindow.ScrollRow = 393
    ActiveWindow.ScrollRow = 376
    ActiveWindow.ScrollRow = 362
    ActiveWindow.ScrollRow = 346
    ActiveWindow.ScrollRow = 333
    ActiveWindow.ScrollRow = 320
    ActiveWindow.ScrollRow = 308
    ActiveWindow.ScrollRow = 296
    ActiveWindow.ScrollRow = 285
    ActiveWindow.ScrollRow = 275
    ActiveWindow.ScrollRow = 263
    ActiveWindow.ScrollRow = 253
    ActiveWindow.ScrollRow = 242
    ActiveWindow.ScrollRow = 229
    ActiveWindow.ScrollRow = 217
    ActiveWindow.ScrollRow = 210
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 180
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    ActiveWindow.SmallScroll Down:=-6
End Sub


Thanks
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
Does this, untested, code work for you?

Code:
Sub Time_Convert_Min()

	Application.ScreenUpdating = false
	
	Dim r as long
	Dim lRow as long

	Columns(3).entirecolumn.insert

	LRow = range("B3").end(xldown).row

	for r = 3 to LRow

		cells(r,3).formulaR1C1 = "=(RC[-1]-R3C2)/60"

	next r

	Application.Goto range("A1"), true

	Application.ScreenUpdating = true

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top