Run-time error '1004' - At least two rows of source data

[Aviles]

Hello,
I'm using the below code which creates a unique list of names from a sheet which holds various names. It works fine, except when the data only contains the one name, which can sometimes happen. I get the following error message: "Run-time error '1004': This command requires at least two rows of source data."

Can some one tell me if there is any way to ge around this error?

When I hit 'Debug' the below code in red is hi-lighted:

Option Explicit
Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
'This macro creates the dynamic drop-down list of Clients/FM in the user form
Dim r As Long
Dim wsL As Worksheet
Dim lLastRow As Long
 
  'Ignore selection of multiple cells
  If Target.Count > 1 Then Exit Sub
 
  'Ensure that we need to register the change
  If Target.Column = 1 And Target.Row > 2 Then
    Set wsL = Sheets("NameLists")
 
    'Clear the old list
    wsL.Range("A1").CurrentRegion.ClearContents
 
    'Get the last row of clients
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
 
    'Create a unique list of clients in the "List" sheet
  [COLOR=red]  Range(Cells(2, 1), Cells(lLastRow, 1)) _[/COLOR]
[COLOR=red]   .AdvancedFilter Action:=xlFilterCopy, _[/COLOR]
[COLOR=red]   CopyToRange:=wsL.Range("A1"), Unique:=True[/COLOR]
    
    'Sort clients alphabetically
    r = wsL.Cells(Rows.Count, 1).End(xlUp).Row
 
    wsL.Range(wsL.Cells(1, 1), wsL.Cells(r, 1)).Sort _
    Key1:=wsL.Range("A1"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
 
    'Dynamically create a named range to capture the client list
    ActiveWorkbook.Names.Add Name:="NameDataValList", _
    RefersToR1C1:="=Lists!R1C1:R" & r & "C1"
 
    'Create a drop down, populated with the unie client list
    With Target.Validation
      .Delete
      .Add Type:=xlValidateList, _
      AlertStyle:=xlValidAlertStop, _
      Operator:=xlBetween, _
      Formula1:="=NameDataValList"
      .IgnoreBlank = True
      .InCellDropdown = True
      .ShowError = False
    End With
 
    End If
End Sub

 

[VoG]

Perhaps like this

If lLastRow <> 2 Then
    Range(Cells(2, 1), Cells(lLastRow, 1)) _
       .AdvancedFilter Action:=xlFilterCopy, _
       CopyToRange:=wsL.Range("A1"), Unique:=True
Else
    wsL.Range("A1").Value = Cells(2, 1).Value
End If

 

[Aviles]

Worked perfectly.. thank you, thank you, thank you!