Hello All,
Please help me solve my problem, I have the attached worksheet, I want to copy all the highlighted time ending in (02).
Examples are:
8/21/2018 12:02:00 PM
8/21/2018 1:02:00 PM
Thank you so much
Hello All,
Please help me solve my problem, I have the attached worksheet, I want to copy all the highlighted time ending in (02).
Examples are:
8/21/2018 12:02:00 PM
8/21/2018 1:02:00 PM
Thank you so much
Use this formula in cell G6 and fill down
Formula:![]()
=IF(ISERROR(MINUTE(A6)),"",IF(MINUTE(A6)=2,1,""))
It will flag all the rows where the Minutes are 02
You can sort by column G to get your selected group.
You can use a macro to extract the data if you want.
![]()
Sub Macro1() LR = Cells(Rows.Count, 1).End(xlUp).Row Range("G6:G" & LR).FormulaR1C1 = _ "=IF(ISERROR(MINUTE(RC[-6])),"""",IF(MINUTE(RC[-6])=2,1,""""))" Range("G6:G" & LR).Value = Range("G6:G" & LR).Value With Range("A1:G" & LR) .HorizontalAlignment = xlGeneral .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveWorkbook.Worksheets("Table 1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Table 1").Sort.SortFields.Add Key:=Range( _ "G1:G" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Table 1").Sort .SetRange Range("A1:G" & LR) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Application.DisplayAlerts = False On Error Resume Next Sheets("Output").Delete Sheets.Add After:=Sheets(1) ActiveSheet.Name = "Output" On Error GoTo 0 Application.DisplayAlerts = True Sheets("Table 1").Select LR = Cells(Rows.Count, 7).End(xlUp).Row Range("A1:G" & LR).Copy Sheets("Output").Range("A1") End Sub
Last edited by mehmetcik; 08-30-2018 at 06:05 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Hello mehmetcik,
Thank you for the reply, sorry for not making myself clear. what I want is copy the whole row or the readings.
Thank you
Run the macro
![]()
Sub Macro1() LR = Cells(Rows.Count, 1).End(xlUp).Row Range("G6:G" & LR).FormulaR1C1 = _ "=IF(ISERROR(MINUTE(RC[-6])),"""",IF(MINUTE(RC[-6])=2,1,""""))" Range("G6:G" & LR).Value = Range("G6:G" & LR).Value With Range("A1:G" & LR) .HorizontalAlignment = xlGeneral .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveWorkbook.Worksheets("Table 1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Table 1").Sort.SortFields.Add Key:=Range( _ "G1:G" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Table 1").Sort .SetRange Range("A1:G" & LR) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Application.DisplayAlerts = False On Error Resume Next Sheets("Output").Delete Sheets.Add After:=Sheets(1) ActiveSheet.Name = "Output" On Error GoTo 0 Application.DisplayAlerts = True Sheets("Table 1").Select LR = Cells(Rows.Count, 7).End(xlUp).Row Range("A1:G" & LR).Copy Sheets("Output").Range("A1") End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks