VBA to copy down formula to last row with data

Hi Experts

I am using the macro below to format and juggle a sheet including merging 2 cells into 1 on each row.

However, the sheet I am using has x amount of rows and I need to carry out this macro on other sheets with varying amount of rows.

Rather than referencing a set number of rows to autofill (in this case 790), how can I adapt the code to only select the "live data" rows so that it will only autofill down to the last live row?

I then have another macro I run to merge all these sheets into 1.

Many thanks

Nick

**************
Sub Working()
    With Selection
           .WrapText = False
    End With
    Range("A:A,B:B,D:D,H:H").Select
    Range("H1").Activate
    Selection.Delete Shift:=xlToLeft
        Range("E1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
    Range("E1").Select
        'option to autofill just data rows required here
    Selection.AutoFill Destination:=Range("E1:E790"), Type:=xlFillDefault
    Range("E1:E790").Select
    Columns("E:E").Select
    Selection.Copy
    Columns("C:C").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:E").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").Select
    Selection.Cut Destination:=Columns("D:D")
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:C").Select
    Columns("A:C").EntireColumn.AutoFit
    Columns("A:B").Select
    Selection.HorizontalAlignment = xlLeft
    Columns("C:C").Select
    Selection.HorizontalAlignment = xlRight
    Range("A1").Select
End SubStart Free Trial