Macro to update filtered data in a column

[tbrynard01]

I have a column with variable number of rows that contains the first and last name of our counselors but I would like to filter each name and update to first name only.

Let's say Column A - as I said the number of rows will vary every day
Peter Pan
Joe Jobs
Peter Pan
Mary Lamb

Manually I can filter and select Peter Pan, update to Peter and copy down the entire range, but would like to be able to do that automatically

I hope that I've explained this clearly and would appreciate any help.

Thanks.

 

[alansidman]

Here is a VBA solution for you.

Option Explicit

Sub Names()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        Range("A" & i) = Left(Range("A" & i), WorksheetFunction.Find(" ", Range("A" & i)))
    Next i
End Sub

 

[Fluff]

How about

Sub tbrynard()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",left(@,find("" "",@)-1))", "@", .Address))
   End With
End Sub

 

[tbrynard01]

Thank you. I will give it try.