how to delete Rows in excel (row times: from~to)

[alimadhi]

hi , i want delete multi rows i have a lots of excels sheets recording time in Ms i want delete between Ms and keep them just every mint recorder

for example:- see blow recorder time in red mark

Time	dwatt2	L33TUC_FLT	l33tuc	DF	l20fa1x	hftout
30/08/2014 15:45	29.10786	0	0	49.17484	0	-5
30/08/2014 15:45	27.9434	0	0	49.19516	0	-5
30/08/2014 15:46	28.25409	0	0	49.21163	0	-5
30/08/2014 15:46	28.33318	0	0	49.2281	0	-5
30/08/2014 15:46	27.60696	0	0	49.23731	0	-5
30/08/2014 15:46	27.64434	0	0	49.2572	0	-5
30/08/2014 15:46	27.96513	0	0	49.27759	0	-5
30/08/2014 15:46	27.61231	0	0	49.29751	0	-5
30/08/2014 15:46	26.07797	0	0	49.33837	0	-5
30/08/2014 15:46	26.06944	0	0	49.36613	0	-5
30/08/2014 15:46	25.04375	0	0	49.40077	0	-5
30/08/2014 15:46	23.88965	0	0	49.41932	0	-5
30/08/2014 15:46	24.33256	0	0	49.44131	0	-5
30/08/2014 15:46	22.60748	0	0	49.45794	0	-5
30/08/2014 15:47	23.06262	0	0	49.47849	0	-5
30/08/2014 15:47	23.36586	0	0	49.48927	0	-5
30/08/2014 15:47	22.28431	0	0	49.49417	0	-5
30/08/2014 15:47	22.8875	0	0	49.50838	0	-5
30/08/2014 15:47	23.01399	0	0	49.51671	0	-5
30/08/2014 15:47	21.87897	0	0	49.54517	0	-5
30/08/2014 15:47	21.91144	0	0	49.56334	0	-5
30/08/2014 15:47	21.88628	0	0	49.57317	0	-5
30/08/2014 15:47	22.12372	0	0	49.58792	0	-5
30/08/2014 15:47	22.22739	0	0	49.59874	0	-5
30/08/2014 15:47	22.05495	0	0	49.6081	0	-5
30/08/2014 15:48	22.24202	0	0	49.61942	0	-5
30/08/2014 15:48	22.25443	0	0	49.62877	0	-5
30/08/2014 15:48	22.32067	0	0	49.63863	0	-5
30/08/2014 15:48	22.31877	0	0	49.64799	0	-5
30/08/2014 15:48	24.25901	0	0	49.65095	0	-5
30/08/2014 15:48	23.55493	0	0	49.64799	0	-5
30/08/2014 15:48	23.61375	0	0	49.64948	0	-5
30/08/2014 15:48	24.74718	0	0	49.65835	0	-5
30/08/2014 15:48	24.82937	0	0	49.66772	0	-5
30/08/2014 15:48	24.81699	0	0	49.67167	0	-5
30/08/2014 15:48	26.04348	0	0	49.67167	0	-5
30/08/2014 15:48	26.08807	0	0	49.67808	0	-5

<tbody>
</tbody>

to :-

Time	dwatt2	L33TUC_FLT	l33tuc	DF	l20fa1x	hftout
30/08/2014 15:45	29.10786	0	0	49.17484	0	-5
30/08/2014 15:46	28.25409	0	0	49.21163	0	-5
30/08/2014 15:47	23.06262	0	0	49.47849	0	-5
30/08/2014 15:48	22.24202	0	0	49.61942	0	-5

<tbody>
</tbody>

 

[Andrew Poulsom]

Can't you just use Remove Duplicates on the Ribbon's Data tab? Here it is in VBA:

Sub Test()
    ActiveSheet.Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[alimadhi]

No Duplicates because the seconds is different

06/09/2014 11:04:13 AM
06/09/2014 11:04:18 AM
06/09/2014 11:04:23 AM
06/09/2014 11:04:28 AM
ETC...

 

[Andrew Poulsom]

Please post your data with the seconds showing. It would have been helpful if you had done that at the outset.

 

[alimadhi]

Time	dwatt2	L33TUC_FLT	l33tuc	DF	l20fa1x	hftout
11:04:13 AM	83.25909	0	1	50.61958	1	100
11:04:18 AM	82.9603	0	1	50.61548	1	100
11:04:23 AM	83.01331	0	1	50.62573	1	100
11:04:28 AM	83.51387	0	1	50.63137	0	100
11:04:33 AM	83.92419	0	1	50.62368	0	100
11:04:39 AM	83.75002	0	1	50.61958	0	100
11:04:44 AM	84.42615	0	1	50.61856	0	100
11:04:49 AM	84.07156	0	1	50.61958	0	100
11:04:54 AM	84.23187	0	1	50.61907	0	100
11:04:59 AM	84.15089	0	1	50.61138	0	100
11:05:04 AM	83.75203	0	1	50.60934	0	100
11:05:09 AM	84.04633	0	1	50.61241	0	100
11:05:14 AM	84.10126	0	1	50.6078	0	100
11:05:20 AM	84.13152	0	1	50.60626	0	100
11:05:25 AM	84.13707	0	1	50.60626	0	100
11:05:30 AM	84.08121	0	1	50.60063	0	100
11:05:35 AM	83.97731	0	1	50.60012	0	100
11:05:40 AM	83.95341	0	1	50.59397	0	100
11:05:45 AM	83.63466	0	1	50.58731	0	100
11:05:50 AM	83.52306	0	1	50.58015	0	100
11:05:55 AM	83.17549	0	1	50.57299	0	100
11:06:00 AM	83.24986	0	1	50.56736	0	100

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

etc... to row 116236

 

[Rick Rothstein]

Give this macro a try...

Sub KeepFirstMinutesOnly()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A3:A" & LastRow) = Evaluate("IF(TEXT(A3:A" & LastRow & _
                            ",""mm/dd/yyyy hh:mm"")=TEXT(A2:A" & LastRow - 1 & _
                            ",""mm/dd/yyyy hh:mm""),"""",A3:A" & LastRow & ")")
  Range("A3:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Select
End Sub

 

[alimadhi]

whoohoo i got this

Time	dwatt2	L33TUC_FLT	l33tuc	DF	l20fa1x	hftout
04:13.0	83.25909	0	1	50.61958	1	100
	82.9603	0	1	50.61548	1	100
	83.01331	0	1	50.62573	1	100
	83.51387	0	1	50.63137	0	100
	83.92419	0	1	50.62368	0	100
	83.75002	0	1	50.61958	0	100
	84.42615	0	1	50.61856	0	100
	84.07156	0	1	50.61958	0	100
	84.23187	0	1	50.61907	0	100
	84.15089	0	1	50.61138	0	100
05:04.2	83.75203	0	1	50.60934	0	100
	84.04633	0	1	50.61241	0	100
	84.10126	0	1	50.6078	0	100
	84.13152	0	1	50.60626	0	100
	84.13707	0	1	50.60626	0	100
	84.08121	0	1	50.60063	0	100
	83.97731	0	1	50.60012	0	100
	83.95341	0	1	50.59397	0	100
	83.63466	0	1	50.58731	0	100
	83.52306	0	1	50.58015	0	100
	83.17549	0	1	50.57299	0	100
06:00.5	83.24986	0	1	50.56736	0	100
	83.37943	0	1	50.56276	0	100
	83.77675	0	1	50.55816	1	100
	84.04648	0	1	50.551	1	100
	83.77617	0	1	50.54743	1	100
	84.11128	0	1	50.54589	0	100
	84.24783	0	1	50.54232	0	100
	83.89067	0	1	50.53925	0	100
	82.85901	0	1	50.53619	0	100
	81.90646	0	1	50.527	0	100
	81.53456	0	1	50.52444	0	100
	81.40382	0	1	50.52036	0	100
07:01.9	81.60603	0	1	50.52342	0	100

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

its good but i want delete rows not empty rows

 

[Rick Rothstein]

So you went from having a date with hours minutes to telling us your times had seconds to what now looks like time only (no date) with decimal minutes. I'm going to sleep now, so I don't have time to test or modify my macro (although I think it should be close)... I check back when I get up and see if your question is still open or not.

 

[alimadhi]

thank you sir

now i have this below data:-

6/9/2014 11:04 AM	83.25909	0	1	50.61958	1	100
6/9/2014 11:05 AM	83.75203	0	1	50.60934	0	100
6/9/2014 11:06 AM	83.24986	0	1	50.56736	0	100
6/9/2014 11:07 AM	81.60603	0	1	50.52342	0	100
6/9/2014 11:08 AM	80.89255	0	1	50.50097	0	100
6/9/2014 11:09 AM	82.85008	0	1	50.45663	0	100
6/9/2014 11:10 AM	83.65703	0	1	50.38697	0	100
6/9/2014 11:11 AM	83.41196	0	1	50.40373	0	100
6/9/2014 11:12 AM	81.49182	0	1	50.40831	1	100
6/9/2014 11:13 AM	82.60677	0	1	50.39205	0	100
6/9/2014 11:14 AM	82.19104	0	1	50.56174	1	100
6/9/2014 11:15 AM	81.55426	0	1	50.55765	0	100
6/9/2014 11:16 AM	83.80222	0	1	50.5643	1	100
6/9/2014 11:17 AM	81.37714	0	1	50.57759	0	100
6/9/2014 11:18 AM	82.50862	0	1	50.55765	0	100
6/9/2014 11:19 AM	81.1102	0	1	50.52904	0	100
6/9/2014 11:20 AM	83.64943	0	1	50.53772	0	100
6/9/2014 11:21 AM	83.55089	0	1	50.5224	0	100
6/9/2014 11:22 AM	82.44308	0	1	50.51525	0	100
6/9/2014 11:23 AM	80.98123	0	1	50.51219	0	100
6/9/2014 11:24 AM	82.01205	0	1	50.49434	0	100
6/9/2014 11:25 AM	81.75157	0	1	50.48567	0	100
6/9/2014 11:26 AM	82.40268	0	1	50.44594	0	100
6/9/2014 11:27 AM	81.54965	0	1	50.42406	0	100
6/9/2014 11:28 AM	81.64493	0	1	50.39611	0	100
6/9/2014 11:29 AM	80.73634	0	1	50.32915	0	100
6/9/2014 11:30 AM	79.99791	0	1	50.30181	0	100

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

I want to delete rows every 10 m to be like below :-

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

Time	dwatt2	L33TUC_FLT	l33tuc	DF	l20fa1x	hftout
6/9/2014 11:10 AM	83.65703	0	1	50.38697	0	100
6/9/2014 11:20 AM	83.64943	0	1	50.53772	0	100
6/9/2014 11:30 AM	79.99791	0	1	50.30181	0	100

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

I want to delete rows every 10 m to be like below :-

<tbody>
</tbody>

Time	dwatt2	L33TUC_FLT	l33tuc	DF	l20fa1x	hftout
6/9/2014 11:10 AM	83.65703	0	1	50.38697	0	100
6/9/2014 11:20 AM	83.64943	0	1	50.53772	0	100
6/9/2014

<tbody>
</tbody>

(I couldn't sleep) give this macro a try...

 Sub KeepEveryTenMinutes()
  Dim LastRow As Long, UnusedColumn As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  UnusedColumn = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
  With Cells(2, UnusedColumn).Resize(LastRow - 1)
    .Formula = "=IF(MROUND(A2,TIME(0,10,0))=A2,A2,"""")"
    .Value = .Value
    .SpecialCells(xlBlanks).EntireRow.Delete
    .Clear
  End With
End Sub