How to Loop Range number in Excel VBA without changing letter

Question

How do I get this to work? I have this code written so far:

Sub RemoveLoop()

Dim i As Long

For i = 6 To 15
If Range("B" + i) = "YES" Then
    Range("C" + i + ":" + "P" + i).ClearContents
End If

Next i
End Sub

Instead of doing each individually like this: This is what I'm trying to shorten/accomplish, below:

Sub Remove()

If Range("B6") = "YES" Then
    Range("C6:P6").ClearContents
End If
If Range("B7") = "YES" Then
    Range("C7:P7").ClearContents
End If
If Range("B8") = "YES" Then
    Range("C8:P8").ClearContents
End If
If Range("B9") = "YES" Then
    Range("C9:P9").ClearContents
End If
If Range("B10") = "YES" Then
    Range("C10:P10").ClearContents
End If
If Range("B11") = "YES" Then
    Range("C11:P11").ClearContents
End If
If Range("B12") = "YES" Then
    Range("C12:P12").ClearContents
End If
If Range("B13") = "YES" Then
    Range("C13:P13").ClearContents
End If
If Range("B14") = "YES" Then
    Range("C14:P14").ClearContents
End If
If Range("B15") = "YES" Then
    Range("C15:P15").ClearContents
End If
End Sub

Simple question for you guys, thank you for your help. I don't know what else to say, it's pretty straight forwards I believe. But I'm still getting the, "Looks like your most is mostly code error." This should be an easy one for you VBA experts to solve.

Thanks again.

Also get into the practice of always setting the parent sheet to ALL range objects; Worksheets("Sheet1").Range..... At least use the ActiveSheet.Range... — Scott Craner
– Scott Craner, Commented Dec 4, 2017 at 14:37

Maldred · Accepted Answer · 2017-12-04 16:01:42Z

2

Try this:

Sub RemoveLoop()

Dim i As Long
Set WSheet = Worksheets("Sheet1") ' This enables the change in the mentioned sheet and not the Active sheet.
For i = 6 To 15
    If WSheet.Range("B" & i) = "YES" Then
        WSheet.Range("C" & i & ":P" & i).ClearContents
    End If
Next i

End Sub

edited Dec 4, 2017 at 16:01

Maldred

1,1144 gold badges12 silver badges35 bronze badges

answered Dec 4, 2017 at 14:41

nishit dey

4581 gold badge7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Craner Over a year ago

You would probably get more up votes if you heeded the my second comment.

nishit dey Over a year ago

I used his code and just modified, for others I can edit the my solution. Thanks Scott. :)

Vityata · Accepted Answer · 2017-12-04 16:04:22Z

0

This is what you can do, passing the cells and the ranges as variables:

Option Explicit

Sub RemoveLoop()

    Dim i As Long

    For i = 6 To 15
        With Worksheets(1)
            If UCase(.Range("B" & i)) = "YES" Then
                .Range(.Cells(i, "C"), .Cells(i, "P")).ClearContents
            End If
        End With
    Next i

End Sub

Except for using Range(Cells,Cells), the code is refering to UCase, which makes sure that "yes" and "YES" in column "B" are treaten equally.

answered Dec 4, 2017 at 16:04

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

How to Loop Range number in Excel VBA without changing letter

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related