Loop that Deletes Rows

Are you adding 10 rows below the number you're searching for? Or clearing the contents from the current 10 rows below the number you're searching for?

so it is supposed to look for the number in AL1 and if it finds it, clear 10 rows, then go on to the number found in AL2? Looking in all columns? AL-AV?

What if the number you're looking for in AL2 gets deleted when the number in AL1 is found? No problem?

Also, would you like the contents cleared from the original cell? or the one the value was found in?

Not exactly sure but try:

Sub test()

Dim l As Long
Dim m As Long
Dim lRow As Long
Dim i As Integer
Dim j As Integer
Dim x As Double
Dim y As Double

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

lRow = Range("AL66536").End(xlUp).Row

Range("AL:AV").Copy
Range("AL:AV").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("AL1").Select

For i = 38 To 48
    For l = 1 To lRow
        Cells(l, i).Select
        If Cells(l, i).Value <> "" Then
        x = Round(Cells(l, i).Value, 3)
            For j = 38 To 48
                For m = ActiveCell.Row + 1 To lRow
                    If IsNumeric(Cells(m, j).Value) = True Then
                        y = Round(Cells(m, j).Value, 3)
                    End If
                    If y = x Then
                        Selection.Offset(1, 0).Select
                        Selection.Resize(10, 1).Select
                        Selection.ClearContents
                    End If
                Next m
            Next j
        End If
    Next l
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Do you have cells with words instead of numbers?

Yes it applies to all columns. Try:

Sub test()

Dim l As Long
Dim m As Long
Dim lRow As Long
Dim i As Integer
Dim j As Integer
Dim x As Double
Dim y As Double

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

lRow = Range("AL66536").End(xlUp).Row

Range("AL:AV").Copy
Range("AL:AV").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("AL1").Select

For i = 38 To 48
    For l = 2 To lRow
        Cells(l, i).Select
        If Cells(l, i).Value <> "" Then
        x = Round(Cells(l, i).Value, 3)
            For j = 38 To 48
                For m = ActiveCell.Row + 1 To lRow
                    If IsNumeric(Cells(m, j).Value) = True Then
                        y = Round(Cells(m, j).Value, 3)
                    End If
                    If y = x Then
                        Selection.Offset(1, 0).Select
                        Selection.Resize(10, 1).Select
                        Selection.ClearContents
                    End If
                Next m
            Next j
        End If
    Next l
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

See attached. Workbook after code is ran.Book21.xlsm

It shouldn't have ran from Z. It ran on the proper columns in the workbook you attached.

The rows below AL37 aren't cleared because the value for it doesn't appear anywhere else. I thought that was the stipulation you wanted?

I thought you wanted to take the value of AL2, search the rest of the columns for that value. If it exists in other columns than clear contents AL3-AL12. Then take the next value (if AL3-12 were cleared it would be AL13, if not it would be AL3) then look for that new value in all the other columns. If it is found then clear the next 10 rows. So on and so forth until you've looked for all values in AL-AV

Well thats much easier . Try:

Sub test()

Dim l As Long
Dim lRow As Long
Dim i As Integer

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

lRow = Range("AL66536").End(xlUp).Row

Range("AL:AV").Copy
Range("AL:AV").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("AL1").Select

For i = 38 To 48
    For l = 2 To lRow
        Cells(l, i).Select
        If Cells(l, i).Value <> "" Then
            Selection.Offset(1, 0).Select
            Selection.Resize(10, 1).Select
            Selection.ClearContents
        End If
    Next l
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

No idea. Its set to run on columns 38 to 48 (Which should always be columns AL to AV even if you have hidden columns).


e/ unless there are more letters in your alphabet