If Statements in Macros in Excel

Question

I originally had a macro written that would clear the specified cells in an excel sheet but I want to be able to only clear certain rows if I need to. This is what my macro looks like currently.

Sub Rectangle1_Click()
response = MsgBox("Are You Sure?", vbYesNo

If response = vbNo Then
    MsgBox ("OK")
    Exit Sub
End If
    Range("A4:C30").Select
    Selection.ClearContents
    Range("G4:H30").Select
    Selection.ClearContents
End Sub

I'm wanting to make it look something more like this though

Sub Rectangle1_Click()
response = MsgBox("Are You Sure?", vbYesNo)

If response = vbNo Then
    MsgBox ("OK")
    Exit Sub
End If
If I4 = 1 then
    Range("A4:C4","G4:H4").Select
    Selection.ClearContents
    Exit Sub
End If
End Sub

And then repeat that code for rows 5-30, when I run the code with multiple if statements it doesn't appear to do anything after the dialogue box pops up. Is there something else that I need to change?

`Range("A4:C30").Select ... Selection.ClearContents` is functionally equivalent to `Range("A4:C30").ClearContents`. There is almost never a good reason to use `Select` and `Selection` in VBA. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). Also, the fact that you are using `I4` without declaring it suggests that you are not using `Option Explicit`. Failing to use that is a recipe for buggy code. — John Coleman, Sep 21 '18 at 16:34
The `I4` in your code, since you haven't `Dim` it, it is a variable with no value. Try `[I4]` then. — newacc2240, Sep 21 '18 at 16:34
Just `If Range("I4").Value = 1 Then Union(Range("A4:C4"),Range("G4:H4")).ClearContents` may do the job in a single line. Or making it really ugly: `If Range("I4").Value = 1 and response = vbYes Then Union(Range("A4:C4"),Range("G4:H4")).ClearContents` will replace both `If` blocks. — JNevill, Sep 21 '18 at 16:37
[I4] fixed the issue thank you for that. I think I don't have this part formatted properly though: Range("A4:C4","G4:H4").Select because it is clearing cells D4 and E4, how would I fix that? — joekeny, Sep 21 '18 at 16:39
Thank you newacc2450 and JNevill! your suggestions made it do what I am wanting the macro to do. — joekeny, Sep 21 '18 at 16:43

score 3 · Answer 1 · answered Sep 21 '18 at 16:39

No .Select required. Also, you do not need to test for vbNo. Just code for vbYes and vbNo does nothing by default

Sub Rectangle1_Click()

response = MsgBox("Are You Sure?", vbYesNo)

If response = vbYes and Range("I4") = 1 Then
    Range("A4:C4","G4:H4").ClearContents
End If

End Sub

If you want to display a message box when they click no you can do something like so (although this seems redundant since the user already said no). If you place the vbNo at the end, you can avoid Exit Sub since this will happen during the natural flow

Sub Rectangle1_Click()

response = MsgBox("Are You Sure?", vbYesNo)

If response = vbYes and Range("I4") = 1 Then
    Range("A4:C4","G4:H4").ClearContents
ElseIf response = vbNo
    Msgbox "OK"
End If

End Sub

score 2 · Accepted Answer · answered Sep 21 '18 at 16:49

2

You can loop through rows 4 -30:

Sub Rectangle1_Click()
    Dim lRow As Long
    If MsgBox("Are you sure?", vbYesNo + vbQuestion) = vbYes Then
        For lRow = 4 To 30
            With ActiveSheet
                If .Cells(lRow, "I").Value = 1 Then
                    .Cells(lRow, "A").Resize(1, 3).ClearContents
                    .Cells(lRow, "G").Resize(1, 2).ClearContents
                End If
            End With
        Next lRow
    Else
        MsgBox "OK", vbOKOnly + vbInformation
    End If
End Sub

answered Sep 21 '18 at 16:49

Olly

7,749
1
19
38

No disrespect, but don't you think that using a variable named `lRow` in this instance could be confusing to noobs. – GMalc Sep 21 '18 at 19:56
No. I don't. It describes exactly what we're looping through. – Olly Sep 21 '18 at 20:01

score 0 · Answer 3 · answered Sep 21 '18 at 16:45

I suggest that you could add a line Option Explicit to make sure all the variables in your code are well defined.

If you want to access a range, use a range object.

Option Explicit

Sub Rectangle1_Click()
    Dim response As Variant
    response = MsgBox("Are You Sure?", vbYesNo)

    If response = vbNo Then
        MsgBox ("OK")
        Exit Sub
    End If

    Dim rngI As Range
    Dim cel As Range
    Set rngI = Range("I4:I30")

    For Each cel In rngI
        If cel.value = 1 Then
            cel.Offset(0, -8).Resize(1, 3).ClearContents 'A:C
            cel.Offset(0, -2).Resize(1, 2).ClearContents 'G:H
        End If
    Next cel
End Sub

If Statements in Macros in Excel

3 Answers3