vba 如果不喜欢vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27470498/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If not like vba
提问by Haikal
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
回答by Alex P
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.
从值列的底部开始向上工作，检查单元格格式并根据需要删除。
回答by Jason Faulkner
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)):
这应该可以工作并且足够灵活以适应一些输入变化（破折号之前或之后的空格，单位或两位数小时输入（没有前导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
回答by Paresh J
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.
但是，为了顺利运行下面的代码，我希望您的时间值格式为TIME。
I have just added additional function IsTimewhich 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 TIMEor not.
我刚刚添加了额外的函数IsTime，它检查单元格值并在单元格值是否为时间值时返回 TRUE/FALSE。在这里您不必担心特定的小时格式，您只需要关心单元格是否格式化为TIME。
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
