Only three minor Changes in VBA Code

[Vishaal]

want to make some changes in below code, need only 3 changes, help pls

we have added the number in code where we need to change.................

1. Selection.AutoFill Destination:= it will select range E and last range of E will be till last filled data in D
2. range("E14:E1779").Select = it will be From E14 to last filled data in D
3. Selection.AutoFill Destination:=Range("HH14:HH1779") - it will select from HH14 and last range will be till last filled data in D

Sub Macro2468()
'
    Range("E14").Select
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC4,R1C:R4C,0)),"""",""Na"")"
    Range("E14").Select
1.    Selection.AutoFill Destination:=Range("E14:E1779")
2.    Range("E14:E1779").Select
    Selection.Copy
    Range("F14:HF14").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Range("HH14").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-211]=""Na"",R[1]C[-211]=""""),COUNTIF(R1C[-211]:RC[-211],""Na"")-SUM(R12C:R[-1]C),"""")"
    Range("HH14").Select
3.    Selection.AutoFill Destination:=Range("HH14:HH1779"), Type:=xlFillDefault
 
     
    Application.ScreenUpdating = True
    With ActiveSheet
        lr = .Cells(Rows.Count, "HH").End(xlUp).Row
        Set Rng = Range("HH14:HH" & lr)
        Rng.Copy
        For i = 217 To 425
            .Cells(14, i).PasteSpecial Paste:=xlPasteFormulas
            With .Cells(14, i).Resize(lr + 1)
                End With
        Next
    End With
    Application.ScreenUpdating = True
 
 
        Cells.Select
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Range("E14").Select

End Sub

 

[Aggie2014]

Someone please correct me if I'm wrong but wouldn't something like this work?

dim last as long
long = Cells(Rows.Count. "D").End(xlUp).Row
' update ranges to a definition similar to ("E14" & last)

 

[Fluff]

How about

   Dim UsdRws As Long
   UsdRws = Range("D" & Rows.Count).End(xlUp).Row
   Range("E14:E" & UsdRws).FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC4,R1C:R4C,0)),"""",""Na"")"


   Range("HH14:HH" & UsdRws).FormulaR1C1 = _
        "=IF(AND(RC[-211]=""Na"",R[1]C[-211]=""""),COUNTIF(R1C[-211]:RC[-211],""Na"")-SUM(R12C:R[-1]C),"""")"