If not like vba [closed]

Question

Closed. This question is off-topic. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 5 years ago.

i wanted to apply a condition (Deleting rows) if a particular cell format is NOT hourly format

I tried the below code but it doesn't work

Sub delete_row()

Dim i As Integer
Dim LR As Long
 LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR
If Not Cells(i, 1).Value Like "##:##-##:##" Then
Cells(n, 1).EntireRow.Delete


End If
Next i
End Sub

Answer 1

Add the below code and I hope this suits your requirement

But, in order to run the below code smoothly, I hope your time value are formatted as TIME.

I have just added additional function IsTime which check the cell value and returns either TRUE/FALSE if cell value is timevalue or not. Here you dont have to worry about particular hour format, you only need to be concern about whether is cell is formatted as TIME or not.

Sub delete_row()

Dim i As Integer
Dim LR As Long
Dim res As Boolean
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR

res = IsTime(Cells(i, 1))

If res = False Then

  Cells(i, 1).EntireRow.Delete

End If
Next i
End Sub


Function IsTime(rng As Range) As Boolean
    Dim sValue As String
    sValue = rng.Cells(1).Text
    On Error Resume Next
    IsTime = IsDate(TimeValue(sValue))
    On Error GoTo 0
End Function

Answer 2

I don't believe there is an out of the box way or a singular pattern match to evaluate a time range the way you are entering it.

This should work though and is flexible enough to accomodate some input variation (spaces before or after the dash, single or double digit hour input (without a leading 0)):

' Check for cell values matching the following Time pattern entry:
' [Hour]:[Minute]-[Hour]:[Minute]    

Dim i As Integer
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Dim valueParts As Variant
Dim result As Boolean
Dim part As Integer

' Because you are deleting rows as you go,
' move from bottom to top.
' Otherwise rows will be skipped when you delete.
For i = LR To 1 Step -1
    ' Split into parts so we can check each.
    valueParts = Split(Cells(i, 1).Value, "-")

    ' Evalutate each component to make sure
    ' it fits the time format we expect.
    ' Default to True until a non-match is found.
    result = True

    For part = LBound(valueParts) To UBound(valueParts)
        ' Account for single digit (0-9)
        ' and double digit (10-12) hours.
        result = result And _
            (Trim(valueParts(part)) Like "#:##" _
            Or Trim(valueParts(part)) Like "##:##")
    Next

    If Not result Then
        ' This is not a valid time pattern.
        ' Delete the row.
        Rows(i).Delete
    End If

Next

Answer 3

I'd do it like this:

Sub DeleteRow()
    Dim i As Integer

    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Range("A" & i).NumberFormat = "hh:mm AM/PM" Then   //Update format to suit your needs
            Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

Start at the bottom of the column of values and work upwards, checking for cell format and deleting as appropriate.