我在VBA中遇到了一个循环问题

I am writing code to search every cell in a range. I want to find all the values of 2 or less characters (e.g. "12" "ab") and then delete those cells. I have it sort of working, but the VBA code is running really slow and only works for 1 column per run. So it won't find everything, just find it in column A, then the code will end without moving onto column B.

我正在编写代码来搜索范围内的每个单元格。我想找到2个或更少字符的所有值(例如“12”“ab”),然后删除那些单元格。我有点工作,但VBA代码运行速度很慢,每次运行仅适用于1列。因此它不会找到所有内容,只需在A列中找到它,然后代码将结束而不会移动到B列。

Here is my code so far:

这是我到目前为止的代码:

Sub test2()
Dim i As Integer
Dim j As Integer

For i = 1 To 524
    For j = 1 To 12
        If Len(Cells(i, j)) <= 2 Then
            Cells(i, j).Delete Shift:=xlToLeft
        End If
    Next j
Next i
End Sub

3 个解决方案

#1

Consider using:

Sub test2()
    Dim i As Integer
    Dim j As Integer

    Application.ScreenUpdating = False
        For i = 1 To 524
            For j = 12 To 1 Step -1
                If Len(Cells(i, j)) <= 2 Then
                    Cells(i, j).Delete Shift:=xlToLeft
                End If
            Next j
        Next i
    Application.ScreenUpdating = True
End Sub

#2

Use a range, so set r=range("a1:l524) and then use for each cell in r, then do the same, if len(r.value)<=2 then cell.delete

使用范围,所以设置r = range(“a1:l524)然后用于r中的每个单元格,然后执行相同的操作,如果len(r.value)<= 2则then.delete

#3

-1

Define the range you want to check and execute the control for each cell of the range

定义要检查的范围,并为该范围的每个单元格执行控制

Public Sub test2()
    Dim cell As Range

    For Each cell In Range("A1:X52")
        If Len(cell) <= 2 Then
            cell.Delete Shift:=xlToLeft
        End If
    Next
End Sub

It should do the work

它应该做的工作

#1