correct macro autofill code for dynamic ranges

[reader6886]

Hello all beginner here.

How do I change the following vb codes so that the macros can be used for sheets with dynamic ranges.

1 Dynamic autofill ( even though autofill was done with autofill dot ) macro reording produced the code with absolute range(G2:G799). How do i make it dynamic.So autofill on other file dont miss any cells or overshoot !

 Range("G2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],65)"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G799")
    Range("G2:G799").Select

2.how do I edit the table range to make it dynamic . Even though using the ctrl+**** + right/ left arrow produced the absolute range($A$1:$F$799) like below.

Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$799"), , xlYes).name = _
        "Table1"
    Range("Table1[#All]").Select

3. what do I use instead of the following code/range(change G2:G1007) so that when used with files with more or less row/columns the formula does not produce unnecessary characters click here in rows with no hyperlinks .

Range("G2").Select
    ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-3],""click here"")"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G1007")
    Range("G2:G1007").Select

thanks in advance for tips.

 

[reader6886]

How about

Range("A1").CurrentRegion.RemoveDuplicates Columns:=4, Header:=xlYes

and

   With ActiveWorkbook.WorkSheets("best movies ever").Range("A1").CurrentRegion
         .Sort .Parent.Range("E1"), xlAscending, , , , , , xlNo
   End With

THANK YOU !!!! works great.
You have taught me how to future proof my macros and make them 'universal' today!

 

[Fluff]

My pleasure.

 

[reader6886]

thank you.

 

[reader6886]