带有循环语句的 Excel VBA 代码问题 - 对象变量或未设置块变量

Question

我正在尝试包含一个代码，该代码将遍历 D 列、搜索 X 条目并执行必要的操作。这应该有效，直到找到带有条目 END 的单元格。 此时，我收到一条错误消息：对象变量或未设置块变量 我做错了什么？

Sub Example
Range("Cl").Select 
Selection.End(xlDown).Select 
ActiveCell.Offset(l, 1).Range("Al").Select 
ActiveCell.FormulaRlCl = "END"
Dim CellsFound As Range 
Range("dl").Select 'ActiveCell.Range("al:al000").Select
Set CellsFound = ActiveCell.Range("al:al000") Do Until ActiveCell = "END"
Cells.Find(What:="X", After:=ActiveCell, Lookin:=xlFormulas, LookAt
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= True, 
SearchFormat:=False).Activate
ActiveCell.Range("Al:Gl").Select Selection.Delete Shift:=xlToLeft
End Sub

Answer 1

我相信以下内容将帮助您实现所需的结果，它使用 For 循环遍历行，检查 D 列中单元格的值，如果 X 则删除 A：G，如果 END 则退出 sub：

Sub Example()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
'declare and set the worksheet you are working with, amend as required
LastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
'get the last row with data on Column D
For i = 2 To LastRow
'loop from Row 2 to the last
If ws.Cells(i, "D").Value = "X" Then ws.Range("A" & i & ":G" & i).Delete Shift:=xlToLeft
'if Column D contains "X" then delete A:G on that row
If ws.Cells(i, "D").Value = "END" Then Exit Sub
'if Column D contains "END" then Exit Subroutine
Next i
End Sub

循环浏览行的替代方法是过滤它们，然后删除任何可见的行，下面是一个示例：

Sub Example()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
'declare and set the worksheet you are working with, amend as required
LastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
'get the last row with data on Column D
ws.Cells.AutoFilter
ws.Range("$A$1:$G$" & LastRow).AutoFilter Field:=4, Criteria1:="X"
'filter Column 4 (D) for any row where it contains "X"
ws.Range("$A$2:$G$" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Shift:=xlLeft
'delete any visible rows after filtering
ws.ShowAllData
'Clear filter criteria
End Sub