Help with a For Loop

[andrewb90]

Hello All,

I'm struggling with getting this right, so hopefully somebody out there can assist me.

I am trying to take the range E89:R106 And with each row, If the Value in Column A of that row is "TRUE", nothing happens, otherwise If FALSE, E:R in that row will have the contents cleared.

Thanks!

Andrew

 

[Tetra201]

Is this what you need?

Sub ClrRng()
Dim rng2clr As Range
For i = 89 To 106 Step 1
    If Not Cells(i, 1) Then
        If rng2clr Is Nothing Then
            Set rng2clr = Range("E" & i & ":R" & i)
        Else
            Set rng2clr = Union(rng2clr, Range("E" & i & ":R" & i))
        End If
    End If
Next i
rng2clr.ClearContents
End Sub

 

[Trebor76]

Hi Andrew,

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()

    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    For lngMyRow = 89 To 106
        If StrConv(CStr((Range("A" & lngMyRow))), vbUpperCase) = "FALSE" Then
            Range("A" & lngMyRow & ":R" & lngMyRow).ClearContents
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[zoliidais]

Hi andrewb90,

Perhaps you could try this code:

Sub LoopingTest()

Dim x As Integer

For x = 89 To 106 'assuming that this is the range you need your macro to run at..

If Cells(x, 1) = "False" Then Range("E" & x & ":R" & x) = ""

Next x

End Sub

Good luck!

 

[andrewb90]

Thank you Tetra, that works really well. One question, Is it possible to have one or two rows excluded from this? Let's say that I extended the range to row 200, but I didn't want rows 120,130, or 140 to be cleared. I could have this code repeated for each range, but i was just wondering if there was perhaps a more elegant way to go about this?

 

[Tetra201]

Here it is:

Sub ClrRng()
Dim rng2clr As Range
For i = 89 To 200 Step 1
    If Not (Cells(i, 1) Or (i = 120) Or (i = 130) Or (i = 140)) Then
        If rng2clr Is Nothing Then
            Set rng2clr = Range("E" & i & ":R" & i)
        Else
            Set rng2clr = Union(rng2clr, Range("E" & i & ":R" & i))
        End If
    End If
Next i
rng2clr.ClearContents
End Sub

 

[andrewb90]

awesome! thank you very much!

 

[Tetra201]

You are welcome.