Jun 4, 2020
2 mins read
最近有个需求,需要删除特定的行,但是这些行不连续。到网上查了半天,只有删除连续的行的,没办法,只能自己动手造轮子了
本来是想遍历行删除,但是发现,如果你删了第一行,那么第二行就会变成第一行了,剩下的行会依次递减,这样第二次的行号就是错的了,所以要换一个思路
思路:先创建一个 LIST,将所有的行号都加到 list 里,然后用APPlication union将每一行作为一个range,合并到一个大的range,然后删除
Sub deleteSpecitalRow(neededDeleteContent, ColumnName As String, Optional ByVal optionColumnName As String)
'new a ArrayList
Dim needDeleteRowIndexList As Object
Set needDeleteRowIndexList = CreateObject("System.Collections.ArrayList")
Dim item
Set sh = ActiveSheet
i = 1
For Each rw In sh.Rows
If i > 6 Then
If sh.Cells(rw.Row, 7).Value = neededDeleteContent Then
needDeleteRowIndexList.Add i
End If
End If
i = i + 1
Next
Dim neededRange As Range
For Each item In needDeleteRowIndexList
If neededRange Is Nothing Then
Set neededRange = sh.Rows(CInt(item)).EntireRow
Else
Set neededRange = Application.Union(neededRange, sh.Rows(CInt(item)).EntireRow)
End If
Next item
neededRange.Select
neededRange.Delete
End Sub
Sharing is caring!