macro only executes to a specific range and then stops

**johnmerlino** · 11-04-2010, 06:25 PM

Hey all,

I have this macro:

Sub Justify_Improved()
'
' Justify_Improved Macro
'

'
    Range("K3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-10]=""DF:"",IF(R[1]C[-9]=""Summary Judgement"",RC[-9],""""))"
    Range("K3").Select
    Selection.AutoFill Destination:=Range("K3:K360"), Type:=xlFillDefault
    Range("K3:K360").Select

    Range("L3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUBSTITUTE(SUBSTITUTE(RC[-1],"", et al"",""""),"", Et Al"","""")"
    Range("L3").Select
    Selection.AutoFill Destination:=Range("L3:L360")
    Range("L3:L360").Select
    Range("L3:L360").Copy
    Range("M3:M360").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1], ""False"", """", 1)"
    Range("N3").Select
    Selection.AutoFill Destination:=Range("N3:N360")
    Range("N3:N360").Select
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1], ""FALSE"", """", 1)"
    Range("N3").Select
    Selection.AutoFill Destination:=Range("N3:N360")
    Range("N3:N360").Select
End Sub

The problem is it executes to a specific range and then stops. So it will stop at row 360, despite the fact that column a can have more rows. Is there a way to get this thing to run the length of column a, where column a has content in its cells?

Thanks for response.

**JBeaucaire** · 11-04-2010, 06:41 PM

Like so:

Option Explicit

Sub Justify_Improved()
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("K3:K" & LR).FormulaR1C1 = _
        "=IF(RC[-10]=""DF:"",IF(R[1]C[-9]=""Summary Judgement"",RC[-9],""""))"
    
    Range("L3:L" & LR).FormulaR1C1 = _
        "=SUBSTITUTE(SUBSTITUTE(RC[-1],"", et al"",""""),"", Et Al"","""")"
    Range("L3:L" & LR).Copy
    Range("M3").PasteSpecial xlPasteValues
        
    Range("N3:N" & LR).FormulaR1C1 = "=SUBSTITUTE(RC[-1], ""FALSE"", """", 1)"

End Sub