Excel VBA-需要循环删除单元格值0的行以上的整个3行

Question

x1,y1,z1,i1
  ,  ,z2,i2
  ,  ,z3,i3
****Blank row****
****Blank row****
****Blank row****
x2,y2,z4,i4,
  ,  ,z5,i5,
  ,  ,z6,i6,

^above would represent the last row for demontration purposes, but the real
sheet extends up 1000s of rows

x1,y1,z1,i1
  ,  ,z2,i2
  ,  ,z3,i3
x2,y2,z4,i4
  ,  ,z5,i5
  ,  ,z6,i6

Sub test()

Dim currentSht As Worksheet
Dim erow
Dim i As Integer

Set currentSht = ActiveWorkbook.Sheets(1)
erow = currentSht.Cells(Rows.Count, 3).End(xlUp).Row

For i = erow To 3 Step -3
If currentSht.Cells(i, 1).Value <> " " Then
    currentSht.Range(Cells(i, 1).Offset(-1), Cells(i, 1).Offset(-3)).EntireRow.Delete
End If
Next i

End Sub

Answer 1

这是因为您一直在测试同一行，并删除上面的3行。

假设i为30，解析为true，就像第2列中有数据一样。我们删除它上面的三行，使当前行为第27行，然后i迭代-3，并且i为27，它测试相同的数据，并对相同的数据再次解析为true。

因此，我们需要强制i更改行号：

Sub test()

Dim currentSht As Worksheet
Dim erow
Dim i As Integer

Set currentSht = ActiveWorkbook.Sheets(1)
erow = currentSht.Cells(Rows.Count, 3).End(xlUp).Row

For i = erow To 3 Step -1
If Trim(currentSht.Cells(i, 1).Value) <> "" Then
    currentSht.Range(Cells(i, 1).Offset(-1), Cells(i, 1).Offset(-3)).EntireRow.Delete
    i = i - 3
End If
Next i