I would like to understand how I can check for a specific pattern in excel using VB script or functions.

The objective is to filter out the MAC which doesn't have a timestamp with exact 30 minute intervals. The accepted entries are only xx:00:00:00:00 or xx:30:00:00:00. Any other MAC with a different timestamp needs to be filtered out. For instance, In the below case, I need to filter out row numbers 8,9 and 10. Would you suggest using Regular expression Excel macro or a excel formula that is even simpler? ( eg: COUNTIF or COUNTIFS)?

CodePudding user response：

If your column P is a real numeric date/time format

Use the following forumula in a helper column

=IF(AND(OR(MINUTE(P2)=0,MINUTE(P2)=30),SECOND(P2)=0),"OK","Sort Out")

and filter that helper column by OK.

If column P is text

Use the following formula in a helper column

=IF(AND(OR(VALUE(MID(P2,15,2))=0,VALUE(MID(P2,15,2))=30),VALUE(MID(P2,18,2))=0),"OK","Sort Out")

and filter that helper column by OK.

CodePudding user response：

If, looking to the rows you say to be eliminated, I correctly understood what you need, please try the next VBA solution:

Sub eliminateSomeRows()
 Dim sh As Worksheet, lastR As Long, arrP
 Dim rngDel As Range, i As Long, arrCh
 
 Set sh = ActiveSheet
 lastR = sh.Range("P" & sh.rows.count).End(xlUp).row
 arrP = sh.Range("P2:P" & lastR).value
 For i = 1 To UBound(arrP)
    arrCh = Split(arrP(i, 1), ":")
    If (arrCh(1) <> "00" And arrCh(1) <> "30") Or _
       left(arrCh(2), 2) <> "00" Then
            If rngDel Is Nothing Then
                 Set rngDel = sh.Range("P" & i   1)
            Else
                Set rngDel = Union(rngDel, sh.Range("P" & i   1))
            End If
    End If
 Next i
 If Not rngDel Is Nothing Then rngDel.EntireRow.Select
End Sub

It will (only) select the rows to be deleted. If you are satisfied with the result, please replace Select, in the last code line, with Delete.

The code should be very fast, using arrays and working in memory, the rows deletion being done at once...