Simple Macro

[Willmonbo]

I’d greatly appreciate if someone can help me get the Macro code for what I need.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I am working with range A4:I60 and what I need the macro to do is as follows;<o></o>
<o> </o>
if the value in I4 is = or greater than 1 it should clear the content of Cell A4, C4 and H4, and if I4 is less than one it should go to row 5 and check the value in I5 if its = or greater than 1 it should clear the content of Cell A5, C5 and H5, I just used row 4 and 5 as the example but this process should be repeated for all rows down to row 60. <o></o>
<o> </o>

 

[Trebor76]

Hi Willmonbo,

Try this:

Sub Macro1()

    Dim rngCell As Range, _
        rngDataSet As Range
        
    Set rngDataSet = Range("I4:I60")
    
    Application.ScreenUpdating = False
    
    For Each rngCell In rngDataSet
        If rngCell.Value >= 1 Then
            Range("A" & rngCell.Row).ClearContents
            Range("C" & rngCell.Row).ClearContents
            Range("H" & rngCell.Row).ClearContents
            rngCell.ClearContents
        End If
    Next rngCell
    
    Application.ScreenUpdating = True
            
End Sub

HTH

Robert

 

[Willmonbo]

Thanks Robert works like a charm.

 

[Willmonbo]

Hi Robert,

it just occurred to me that I should ideally add two more things to this Macro, the one main thing is that a confirmation message should appear before it runs the macro, and the second less important one is that it should also clear the content of the cell if the value = or is greater than "negative 1' (-1), I truly appreciate your help.

 

[Trebor76]

Hi Willmonbo,

Post back re how this goes:

Sub Macro1()

    Dim rngCell As Range, _
        rngDataSet As Range
    Dim strResponse As String
    
    strResponse = MsgBox("Are you sure you'd like to run the macro which will potentially clear some cells?", vbYesNo + vbQuestion, "Clear Contents Editor")
    
    Select Case strResponse
        Case vbYes
            Set rngDataSet = Range("I4:I60")
                
                Application.ScreenUpdating = False
    
                    For Each rngCell In rngDataSet
                        If rngCell.Value >= -1 Then
                            Range("A" & rngCell.Row).ClearContents
                            Range("C" & rngCell.Row).ClearContents
                            Range("H" & rngCell.Row).ClearContents
                            rngCell.ClearContents
                        End If
                    Next rngCell
    
                Application.ScreenUpdating = True
        Case vbNo
            MsgBox "No action has been taken.", vbInformation, "Clear Contents Editor"
    End Select

End Sub

Regards,

Robert

 

[Willmonbo]

the fact that we added the -1 clears the content of all cells in the entire range, what I meant is that it should clear when the value is either >= 1 on the positive side or <= 1 on the negative side, I hope this time around I explained myself a bit better

 

[Trebor76]

Ah, sorry about that.

Try this (on a copy of your data in case it's not as expected as well):

Sub Macro1()

    Dim rngCell As Range, _
        rngDataSet As Range
    Dim strResponse As String
    
    strResponse = MsgBox("Are you sure you'd like to run the macro which will potentially clear some cells?", vbYesNo + vbQuestion, "Clear Contents Editor")
    
    Select Case strResponse
        Case vbYes
            Set rngDataSet = Range("I4:I60")
                
                Application.ScreenUpdating = False
    
                    For Each rngCell In rngDataSet
                        With rngCell
                            'Clear contents if the cell value is equal to or less than -1, _
                            or it's equal to or greater than 1 i.e. only leave values between _
                            -0.999... to 0.999...
                            If .Value >= 1 Or .Value <= -1 Then
                                Range("A" & .Row).ClearContents
                                Range("C" & .Row).ClearContents
                                Range("H" & .Row).ClearContents
                                .ClearContents
                            End If
                        End With
                    Next rngCell
    
                Application.ScreenUpdating = True
        Case vbNo
            MsgBox "No action has been taken.", vbInformation, "Clear Contents Editor"
    End Select

End Sub

Regards,

Robert

 

[Willmonbo]

Robert,

It works just like imagined, can't thank you enough.

 

[Trebor76]

You're welcome