Autofill from active cell to last row 1

[Evil8]

I need to insert a formula from the active cell down the column to the last row. My problem is with the last line of code below.

I have:

Sub ClientName()
' SepatateName Macro
' Create first name and last name from full name.
'
 Dim LastRow As Long, r As Long        'Last Row
 Dim NCol As Long                      'Client Name Column

  'Find the LAST real row
    LastRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row
'

    NCol = Cells.Find(What:="Client Name", SearchOrder:=xlByColumns, MatchCase:=True).Column
    Columns(NCol).Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Range(Selection, Selection.Columns(4)).EntireColumn.Select
    Selection.ColumnWidth = 20
    ActiveCell.FormulaR1C1 = "Last Name"
    With ActiveCell.Characters(Start:=1, Length:=9).Font
        .Name = "Arial"
        .FontStyle = "Bold"
    End With

    ActiveCell.Offset(1, 0).Select
    ActiveCell.Formula = "=LEFT(RC[+4],FIND("","",RC[+4])-1)"
    Selection.AutoFill Destination:=Range([COLOR=red]ActiveCell[/color] & LastRow), Type:=xlFillDefault

    
End Sub

All your help is appreciated.

Evil8

 

[Bong]

The syntax for a range is range(cell1,cell2). lastrow is a row number. The corresponding column number is, I believe, the column number of activecell:

colnum=activecell.column

that is, if you want to perform this all on a single column.
So, I think you need:

Destination:=Range(ActiveCell,cells(LastRow, activecell.column))

_________________
Bob Rashkin

 

[Evil8]

That worked perfectly. Thank you very much!