![Excel 2013 - 根据特定单元格引用删除行](https://linux22.com/image/1533357/Excel%202013%20-%20%E6%A0%B9%E6%8D%AE%E7%89%B9%E5%AE%9A%E5%8D%95%E5%85%83%E6%A0%BC%E5%BC%95%E7%94%A8%E5%88%A0%E9%99%A4%E8%A1%8C.png)
我正在尝试弄清楚如何在 Excel 2013 中删除单元格内容以数字开头的行。
例如,在 HI 列中有多个条目,一些以字母开头,一些以数字开头。我想删除 H 列中单元格内容以数字开头的所有行。
如能得到任何指导/建议我将不胜感激!
好的,我创建了一些 VBA 代码,它似乎可以在一张纸上运行:
Sub Macro_01()
Dim L As Long, i As Long
L = Cells(Rows.Count, "F").End(xlUp).Row
For i = L To 1 Step -1
If Left(Cells(i, "F"), 1) Like "[0-9]" Or Cells(i, "F") = "" Then
Rows(i).Delete
End If
Next
End Sub
我正在尝试弄清楚如何让这个功能在工作簿中的所有 284 张工作表上工作。我试过 Do/Loop,但它只是无休止地运行(并且停止响应)。因为我知道有多少张工作表,所以我需要在代码中输入一个简单的命令吗?
答案1
这是如何工作的?(我假设你发布的代码本身按预期工作,只需要运行它全部頁面。
Sub Macro_01()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim L As Long, i As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
L = .Cells(Rows.Count, "F").End(xlUp).Row
For i = L To 1 Step -1
If Left(.Cells(i, "F"), 1) Like "[0-9]" Or .Cells(i, "F") = "" Then
.Rows(i).Delete
End If
Next
Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
只需循环遍历每张表,确保重置最后一行变量。然后关闭计算/屏幕更新,直到最后。请注意,如果您有 200 多张表,这确实可能需要一些时间。
最后,请确保首先在数据副本上运行此操作,因为您可能知道,无法撤消宏。
答案2
感谢您迄今为止的帮助,我想我已经解决了它:
Sub Macro_01()
ActiveSheet.Next.Select
Do
Dim L As Long, i As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
L = Cells(Rows.Count, "F").End(xlUp).Row
For i = L To 1 Step -1
If Left(Cells(i, "F"), 1) Like "[0-9]" Or Cells(i, "F") = "" Then
Rows(i).Delete
End If
Next
Next ws
If ActiveSheet.Index <> Sheets.Count Then
ActiveSheet.Next.Select
Else
Exit Do
End If
Loop
End Sub