颜色和删除
- 如果您添加了
Sub
和End Sub
并定义了lngRow
,它将成为一个最小可重现示例
。
- 我更喜欢有意义的变量名,比如
LastRow
,而不是匈牙利命名法中的lngRow
,现在在这个网站上很少见。
- 当您在一行中评估两个条件时,两个条件都会被评估。当第一个已经是
False
时,为什么还要评估第二个呢?
- 创建一个像
builtRange
这样的过程,并使用它来“构建”(“组合”)范围,将比每次访问工作表更有效。使用它将只减少对工作表的访问一次(每个操作)。
- 最后一个过程只是关于如何针对非空单元格(而不是非空单元格)进行测试的想法。其中包括,最好使用
If Len(cel.value) > 0 Then
来完成。它创建一个新的工作簿并在其中执行操作,因此所有其他工作簿都是安全的。
代码
Option Explicit
Sub colorOrDelete()
Dim LastRow As Long: LastRow = 20
Dim drg As Range ' Delete Range
Dim crg As Range ' Color Range
Dim i As Long ' Worksheet Rows Counter
For i = 2 To LastRow
If Not IsEmpty(Cells(i, 27)) Then
If Cells(i, 27).Value <> "Completed" Then
If Cells(i, 28).Value = "Contact Information Not Found" Then
If Len(Cells(i, 4).Value) = 4 Then
If Left(Cells(i, 4), 1) = "F" Then
buildRange crg, Cells(i, 28)
Else
buildRange drg, Rows(i)
End If
End If
End If
' An Idea
'ElseIf Cells(i, 28).Value = "Invalid Account Number." Then
'Else
End If
End If
Next i
Dim hasChanged As Boolean
Application.ScreenUpdating = False
' You wanna color the cells before deleting the rows.
If Not crg Is Nothing Then
hasChanged = True
crg.Interior.Color = RGB(255, 0, 0)
End If
If Not drg Is Nothing Then
If Not hasChanged Then
hasChanged = True
End If
drg.Delete
End If
Application.ScreenUpdating = True
If hasChanged Then
MsgBox "Operation finished.", vbInformation, "Success"
Else
MsgBox "Done nothing.", vbExclamation, "No Change"
End If
End Sub
Sub buildRange( _
ByRef builtRange As Range, _
AddRange As Range)
If builtRange Is Nothing Then
Set builtRange = AddRange
Else
Set builtRange = Union(builtRange, AddRange)
End If
End Sub
一些想法
Sub buildRangeParanoia( _
ByRef builtRange As Range, _
AddRange As Range)
If Not AddRange Is Nothing Then
If builtRange Is Nothing Then
Set builtRange = AddRange
Else
If AddRange.Worksheet Is builtRange.Worksheet Then
Set builtRange = Union(builtRange, AddRange)
End If
End If
End If
End Sub
Sub EmptyVsBlank()
Dim wb As Workbook: Set wb = Workbooks.Add
With wb.Worksheets(1)
Dim cel1 As Range: Set cel1 = .Range("A1"): cel1.Value = Empty
Dim cel2 As Range: Set cel2 = .Range("A2"): cel2.Value = "="""""
Dim cel3 As Range: Set cel3 = .Range("A3"): cel3.Value = "'"
Debug.Print IsEmpty(cel1), IsEmpty(cel1.Value), Len(cel1.Value)
Debug.Print IsEmpty(cel2), IsEmpty(cel2.Value), Len(cel2.Value)
Debug.Print IsEmpty(cel3), IsEmpty(cel3.Value), Len(cel3.Value)
Dim rg As Range: Set rg = .Range("A1:A3")
Debug.Print Application.CountA(rg) ' A2, A3
Debug.Print Application.CountBlank(rg) ' A1, A2, A3
' The Shock
Debug.Print Application.CountIf(rg, Empty) ' None
Debug.Print Application.WorksheetFunction.CountIf(rg, Empty) ' None
.Parent.Saved = True ' To close without prompt.
'.Parent.Close SaveChanges:=False
End With
' Result:
' True True 0
' False False 0
' False False 0
' 2
' 3
' 0
' 0
End Sub