excel,宏,删除指定条件的行,删除指定行,代码

如图所示→1:想删除从A列的k1115开始A列到最后一个※为止的所有行。2:想删除B列以0开始的所有行。
excelhonedelline
实现代码:
Sub 删除指定条件的行()
‘删除K1115到最后一个※号,删除B列首字符是0
endrow = Range(“A65536”).End(xlUp).Row
For i = endrow To 1 Step -1
    If Cells(i, 1) = “※” Then zz = i
    If Cells(i, 1) = “K1115” Then qs = i
Next
If zz <> “” And qs <> “” Then
    Range(“A” & qs & “:” & “A” & zz).EntireRow.Delete
Else
    MsgBox “A列没有找到符合条件的内容!”
End If
endrow = Range(“B65536”).End(xlUp).Row
For i = endrow To 1 Step -1
    If Left(Cells(i, 2), 1) = “0” Then Cells(i, 2).EntireRow.Delete
Next

End Sub

下面的代码是实现删除指定行

Sub delblank1()
Dim iRstar As Long: iRstar = 1  ‘第1个需删除的行号
Dim iRend As Long: iRend = 14  ‘数据区域的末尾行号,可以是间隔的中间位置
Dim iRcount As Long: iRcount = 13 ‘间隔的行数,即交替删除与保留的行数量
Dim iRng As Range, iR As Long, i As Long
Columns(1).Insert
With Range(“A1”)
‘以上,1到14行,删除13行

.Value = 1

.AutoFill Destination:=Range(“A1:A” & iRend + 1), Type:=xlFillSeries

.EntireColumn.AutoFit
End With
iR = iRstar
Set iRng = Rows(iR & “:” & iR + iRcount – 1)
For i = 2 To Int((iRend – iRstar + 1) / iRcount) – 1 Step 2

iR = iRstar + iRcount * i

Set iRng = Union(iRng, Rows(iR & “:” & iR + iRcount – 1))
Next
iR = ((iRend – iRstar + 1) Mod (iRcount * 2))
If iR > 0 And iR < iRcount Then Set iRng = Union(iRng, Rows(iRend – iR + 1 & “:” & iRend))
iRng.Delete
End Sub
Sub delblank2()
Dim iRstar As Long: iRstar = 31  ‘第1个需删除的行号
Dim iRend As Long: iRend = 34  ‘数据区域的末尾行号,可以是间隔的中间位置
Dim iRcount As Long: iRcount = 2 ‘间隔的行数,即交替删除与保留的行数量
Dim iRng As Range, iR As Long, i As Long
Columns(1).Insert
With Range(“A1”)

.Value = 1

.AutoFill Destination:=Range(“A1:A” & iRend + 1), Type:=xlFillSeries

.EntireColumn.AutoFit
End With
iR = iRstar
Set iRng = Rows(iR & “:” & iR + iRcount – 1)
For i = 2 To Int((iRend – iRstar + 1) / iRcount) – 1 Step 2

iR = iRstar + iRcount * i

Set iRng = Union(iRng, Rows(iR & “:” & iR + iRcount – 1))
Next
iR = ((iRend – iRstar + 1) Mod (iRcount * 2))
If iR > 0 And iR < iRcount Then Set iRng = Union(iRng, Rows(iRend – iR + 1 & “:” & iRend))
iRng.Delete
End Sub

Tags: , , , ,

  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • Twitter
  • RSS

Leave a Reply

You must be logged in to post a comment.

文字链