删除列数据为零的行

I am trying to clean up some spreadsheet data, as such I had the below code to delete all rows for which column 10 has the number 0. The problem is when I run the code it deletes all the rows.

我正在清理一些电子表格数据，因此我有下面的代码来删除列10中编号为0的所有行。问题是，当我运行代码时，它会删除所有行。

Would anyone be able to highlight what exactly is wrong with my code?

有人能指出我的代码到底出了什么问题吗?

Sub Format()

Dim Fund As Long 
Dim TotalLoop As Long

RowCount = Range("A65536").End(xlUp).Row
For TotalLoop = RowCount To 2 Step -1
Fund = Cells(TotalLoop, 10)
If Fund = 0 Then
Rows(TotalLoop).Delete
Cells(TotalLoop - 1, 10) = Fund
End If
Next TotalLoop

End Sub

3 个解决方案

#1

Just remove the below line and execute your code. It will work

只需删除下面的行并执行您的代码。它将工作

Cells(TotalLoop - 1, 10) = Fund

#2

In your code, you are changing the value of the next undeleted row to the value of fund, which is zero in order for the code to execute. Remove the line Cells(TotalLoop - 1, 10) = Fund and your loop will function as desired

在您的代码中，您正在将下一个未删除行的值更改为fund的值，该值为零，以便代码执行。删除线单元格(TotalLoop - 1, 10) =基金，您的循环将按需要运行。

#3

Sub Format()

子格式()

Dim sh As Worksheet
Dim Fund As Long
Dim TotalLoop As Long
Set sh = ThisWorkbook.Sheets("Sheet1")
RowCount = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count


For TotalLoop = RowCount To 2 Step -1
Fund = Cells(TotalLoop, 10)
If Fund = 0 Then
Rows(TotalLoop).Delete
End If
Next TotalLoop

End Sub

终止子

#1