CODEWITHSUNDEEP
vbaexcel
AutomateMyJob
AutomateMyJob

Reputation: 307

I'm having trouble with a loop in 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.

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

Upvotes: 3

Views: 73

Answers (3)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96781

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

Upvotes: 2

Nathan_Sav
Nathan_Sav

Reputation: 8531

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

Upvotes: 0

Maxime Port&#233;
Maxime Port&#233;

Reputation: 1074

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

Upvotes: -1

Related Questions