VBA程序无法运行得到结果

难道这还有 · 发表于 2017-4-21 10:07

我写了一个为了清除某些过大数据的VBA程序，但是我的程序不能一次将同一列里面所有过大的数据一起清除，只能一个一个清除。

Public Function Removechar()
Dim End_Row_No As Long
Dim Row_Index As Integer
Dim RowDelete As Integer
For End_Row_No = 2 To 50000
If Cells(End_Row_No, 1).Value = "" Then Exit For
Next End_Row_No
For Row_Index = 2 To End_Row_No Step 1
For RowDelete = 1 To Row_Index - 1 Step 1
If RowDelete >= Row_Index Then
Exit For
End If
If Cells(RowDelete, 2).Value = Cells(Row_Index, 2).Value Then
Rows(RowDelete).Delete
RowDelete = RowDelete - 1
If Row_Index > 4 Then
Row_Index = Row_Index - 1
End If
End If
Next RowDelete
If Cells(Row_Index, 2).Value = "" Or Cells(Row_Index, 2).Value = Empty Then Exit For
Next Row_Index
For i = 2 To 10000
If IsEmpty(ActiveSheet.Cells(i, 1)) Then
RowQty = i
Exit For
End If
Next i
RowQty = RowQty - 1
For j = 2 To RowQty
For k = 4 To 18
SpacePos = InStr(1, ActiveSheet.Cells(j, k), " ", vbTextCompare)
If SpacePos = 0 Then
If (Len(ActiveSheet.Cells(j, k)) > 0) Then
If (Asc(ActiveSheet.Cells(j, k)) < 65) Then
ActiveSheet.Cells(j, k) = Val(ActiveSheet.Cells(j, k))
End If
End If
Else
ActiveSheet.Cells(j, k) = Val(Left(ActiveSheet.Cells(j, k), SpacePos - 1))
End If
Next k
Next j
End Function
Sub Correct_Number()
Application.ScreenUpdating = False
Set sht1 = ThisWorkbook.Worksheets("TEC_PLUS")
Set sht2 = ThisWorkbook.Worksheets("SiB_htr_res")
Set sht3 = ThisWorkbook.Worksheets("Thermistor_res")
Dim success As Boolean
sht1.Activate
success = Removechar()
sht2.Activate
success = Removechar()
sht3.Activate
success = Removechar()
Dim m As Integer
For m = 2 To 10000
If sht1.Cells(m, 4).Value > 5 Then
sht1.Rows(m).Delete
End If
Next
'-------------------
Dim n As Integer
For n = 2 To 10000
If sht2.Cells(n, 5).Value > 220 Then
sht2.Rows(n).Delete
End If
Next
'-------------------
Dim o As Integer
For o = 2 To 10000
If sht3.Cells(o, 4).Value > 120000 Then
sht3.Rows(o).Delete
End If
Next
'-------------------
ThisWorkbook.Charts("X3GG383CE").Activate
Application.ScreenUpdating = True
End Sub

复制代码

cflood · 发表于 2017-4-21 10:20

function里不能删除行

难道这还有 · 发表于 2017-4-21 10:53

cflood 发表于 2017-4-21 10:20
function里不能删除行

但是我将那一段函数都注释了还是没用啊，一样的效果

难道这还有 · 发表于 2017-4-21 11:36

跪求大神帮助

难道这还有 · 发表于 2017-4-21 13:03

自顶！顶！顶！顶！

lsc900707 · 发表于 2017-4-21 13:30

删除数据要倒着删才能干净彻底。

lsdongjh · 发表于 2017-4-21 13:37

楼上正解，删除必须倒序！！
For n = 10000 To 2 step -1

难道这还有 · 发表于 2017-4-21 13:43

lsc900707 发表于 2017-4-21 13:30
删除数据要倒着删才能干净彻底。

能解释一下么

难道这还有 · 发表于 2017-4-21 13:46

lsdongjh 发表于 2017-4-21 13:37
楼上正解，删除必须倒序！！
For n = 10000 To 2 step -1

能解释一下顺着删和倒着删的区别么

lsdongjh · 发表于 2017-4-21 16:41

难道这还有发表于 2017-4-21 13:46
能解释一下顺着删和倒着删的区别么

自己看图吧：

		自动登录	找回密码
密码			免费注册