删除行，除了符合某些条件的行

Question

我已经做了一个For Each循环，将保留数据取决于一些标准，但我不知道如何格式化它，所以如果列a中的单元格包含正好8个数字，那么它将保留行。

的例子:

单元格A289包含:04245468←保留这行

单元格A978包含:04513←删除此行

到目前为止，我在代码中使用左边的函数有以下内容:

Sub CleanUpSheet1()
Dim RowA As Range
'hides any popups
Application.DisplayAlerts = False
'Deletes all blanks up to row 15,0000
Range("a2:A15000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
For Each RowA In Range("A2:A" & ActiveSheet.UsedRange.Rows.Count)
If Left(RowA.Value, 5) <> "issue" And Right(RowA.Value, 4) <> "-000" And RowA.Value <> 0 Then
RowA.EntireRow.Delete
End If
Next
're-enables popups
Application.DisplayAlerts = True 
End Sub

Answer 1

如果它只是一个数字问题，您可以将单元格值转换为字符串(RowA)。文本或Cstr(RowA.Value)，然后测试长度:

如果len(cstr(RowA.Value)) = 8 ??(或len (RowA.Text))

如果我没有理解你的问题，请告诉我。

Answer 2

必须重新格式化。

Sub CleanUpSelect1()
Dim RowA As Range
Dim cRng As Range
Dim iCounter As Long
Set RowA = Sheet1.Range("A2:A" & Sheets("Sheet1").UsedRange.Rows.Count)
iCounter = 1
Do
Set cRng = RowA(iCounter, 1) 'Loop rows
If CStr(cRng.Value2) = vbNullString Then
cRng.EntireRow.Delete
Else
If Not KeepRow(cRng.Value2) Then
cRng.EntireRow.Delete  'Delete row
Else
iCounter = iCounter + 1 'Advance
End If
End If
Loop While iCounter <= RowA.Rows.Count 'Stop after loop all rows
End Sub
Private Function KeepRow(RowValue As String) As Boolean
KeepRow = (RowValue Like "########" Or RowValue Like "issue#" Or RowValue Like "######-###")
End Function

但它有效。