VBA If statement

[Pinaceous]

Hi All,

I'm working with Columns H & I in Range from rows 11 to 180.

How would I write this code below in lieu of writing out each line of its range:

If Range("H11") = "Enter Tally" And Range("I11") = "" Then
Range("H11").ClearContents
End If

If Range("H12") = "Enter Tally" And Range("I12") = "" Then
Range("H12").ClearContents
End If

If Range("H13") = "Enter Tally" And Range("I13") = "" Then
Range("H13").ClearContents
End If

.
.
.

If Range("H180") = "Enter Tally" And Range("I180") = "" Then
Range("H180").ClearContents
End If

For example, how would I shorten its length of my proposed code in lieu of writing out each and every line until row 180?

Please let me know when you get a chance.

Thanks!
pinaceous

 

[Phuoc]

Try this

Public Sub Test()
Dim i As Long
For i = 11 To 180 Step 1
    If Range("H" & i).Value = "Enter Tally" And Range("I" & i).Value = "" Then
        Range("H" & i).ClearContents
    End If
Next i
End sub

 

[Pinaceous]

Try this

Public Sub Test()
Dim i As Long
For i = 11 To 180 Step 1
    If Range("H" & i).Value = "Enter Tally" And Range("I" & i).Value = "" Then
        Range("H" & i).ClearContents
    End If
Next i
End sub

Many Thanks Phuoc!!!

 

[My Aswer Is This]

Try this:
There are other ways but to show you how to do it your way:

Sub My_Script()
'Modified  6/21/2022  11:48:43 PM  EDT
Dim i As Long
Application.ScreenUpdating = False

    For i = 11 To 180
        If Cells(i, "H").Value = "Enter Tally" And Cells(i, "I").Value = "" Then Cells(i, "H").ClearContents
    Next
Application.ScreenUpdating =True
End Sub