Excel Looping Macro

[Ryanblades]

Hello all,

I am in need of looping the macro code below

Sub Test()
If Range("D2") = "Secondary" Then Range("J2,I2,R2,S2,T2").ClearContents
End Sub

The code above works great for the one line it is clearing but..

I need it to test conditions from 2 to 6000 on each column
If Range("D2") = "Secondary" Then Range("J2,I2,R2,S2,T2").ClearContents
...
...
If Range("D6000") = "Secondary" Then Range("J6000,I6000,R6000,S6000,T6000").ClearContents

Any way to loop this without having to hand type every line?

Thank you for any help

 

[Norie]

Try this.

Sub Test()
Dim I As Long
    
    For I = 2 To 6000
        If Range("D" & I).Value = "Secondary" Then Intersect(Range("I:J,R:T"), Rows(I)).ClearContents
    Next I

End Sub

 

[gallen]

Norie's is much more succinct.

I like the use of Intersect there...

This was my effort:

Sub Test()
    Dim c As Range
    
    For Each c In Range("D2:D6000")
        If c = "Secondary" Then Range("J" & c.Row & ",I" & c.Row & ",R" & c.Row & ",S" & c.Row & ",T" & c.Row).ClearContents
    Next c
End Sub

 

[Ryanblades]

This worked 100% how i was needing it too

Thank you for time and expertise