loop through rows with blanks.

[tourless]

Hi Folks.

Working through another simple problem... I need to know how to turn this...
In J3 I have =IF(I3="","",F3-I3)
...into a macro that will either skip the blank rows or just insert the formula as is until it reached my last row of data. I already know my last row via lRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Basically, starting at I3, test if there is a value in I, if so add the formula to J3, if not, move on to the next row until the end of the data is reached.

 

[tourless]

It's not pretty but it works

ActiveSheet.Range("$A$2:$J$" & lRow).AutoFilter Field:=9, Criteria1:="<>"
    
    Range("J3:J" & lRow).Formula2 = "=If(I3="""","""",F3-I3)"
    
    With ActiveSheet
        .AutoFilterMode = False
    End With

 

[johnnyL]

An example approach that leaves no formulas on the sheet and a macro that doesn't require filters or loops:

Sub Test()
'
    Dim lRow    As Long
'
    With Sheets("Sheet1")
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'
        With .Range("J3:J" & lRow)
            .Value = Sheets("Sheet1").Evaluate("If(" & .Offset(0, -1).Address & "="""",""""," & .Offset(0, -4).Address & "-" & .Offset(0, -1).Address & ")")
        End With
    End With
End Sub

 

[tourless]

That's a good one JohnnyL, I appreciate the input.