Dynamic Range

[Aviles]

Hello

I have found the below code which uses range C2:C20.

Can some one please tell me how to increase this range, eg C2:C100?

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
Dim r As Long
Dim wsL As Worksheet
Set wsL = Worksheets("Lists")
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 And Target.Row > 2 Then
wsL.Range("A1").CurrentRegion.ClearContents
Range(Cells(2, 3), Cells(Target.Row - 1, 3)) _
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=wsL.Range("A1"), Unique:=True
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
ActiveWorkbook.Names.Add Name:="DataValList", _
RefersToR1C1:="=Lists!R1C1:R" & r & "C1"
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=DataValList"
.IgnoreBlank = True
.InCellDropdown = True
.ShowError = False
End With
End If
End Sub

 

[GlennUK]

... code which uses range C2:C20

What makes you say that? I see no such range specification.

 

[Aviles]

What makes you say that? I see no such range specification.

The website that I got it from said that it was only used for range C2:C20. I also tried the code which confirms it.

I'm with you, can't see any range specification anywhere.

 

[GlennUK]

The website that I got it from said that it was only used for range C2:C20. I also tried the code which confirms it.

I'm with you, can't see any range specification anywhere.

Can you say what tests you tried with the code ... what data you put in, and what happened when you ran it, and what you think should have happened if there was no range limitation?