我有数据并进行了一些过滤。现在,我想将整个行删除到最后一个可见的行。另外,在这种情况下,我不想包括我的标题(第5行)。我不确定如何处理以下代码:
Dim row1 As Variant
row1 = Rows(5).Offset(1, 0)
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Rows("row1:" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
您需要在使用SpecialCells时添加错误处理程序。
Sub DeleteVisibleRows()
With ActiveSheet
On Error Resume Next
.Range("A5", .Range("A" & .Rows.Count).End(xlUp)).Offset(1).EntireRow _
.SpecialCells(xlCellTypeVisible).Delete
On Error GoTo 0
End With
End Sub
请尝试以下:
Sub test()
Dim lastrow As Long
Dim rng As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Rows("6:" & lastrow)
rng.Delete Shift:=xlUp
End Sub
请尝试一下...
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A6:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
如果您只想在数据集上应用过滤器(
),请尝试此操作。Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
If ActiveSheet.FilterMode Then
On Error Resume Next
Range("A6:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If