Replacement for using Intersect Function and UsedRange to find variable range

[spidaman]

Can anyone help me with a replacement method for assigning a variable range please?

In the data the length of Row 1 is variable and the location of TargetStr1 is also variable.

At the moment I am using the Intersect function and UsedRange but don't think it's the best way to do it:

Sub Estab_Range ()

Dim ws1 As Worksheet
Dim lcol As Long
Dim foundCell As Range, titRng As Range, ListRng As Range
Dim TargetStr1 As String

Set ws1 = ActiveWorkbook.ActiveSheet
lcol = ws1.Range("A1").End(xlToRight).Column
Set titRng = ws1.Range(("A1"), Cells(lcol))

TargetStr1 = "List"

    Set foundCell = titRng.Find(what:=TargetStr1, LookIn:=xlValues, _
    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=True, SearchFormat:=False)

    Set ListRng = Intersect(foundCell.EntireColumn, ws1.UsedRange)

End Sub

Thanks in advance.

 

[Fluff]

Nothing wrong with using Intersect, although, depending on your data, you could also use

   Set ListRng = Range(foundCell, Cells(Rows.Count, foundCell.Column).End(xlUp))

 

[spidaman]

Nothing wrong with using Intersect, although, depending on your data, you could also use

   Set ListRng = Range(foundCell, Cells(Rows.Count, foundCell.Column).End(xlUp))

Thanks Fluff that's what I was looking for. Would the .end(xlUp) method still work ok if there are empty cells in the column within the ListRng?

 

[spidaman]

It was more UsedRange I was trying to avoid I suppose....

 

[Fluff]

Depends on what exactly you mean.
That will find the last cell in the column that is not blank, but other columns may have data in rows further down the sheet.

 

[spidaman]

Depends on what exactly you mean.
That will find the last cell in the column that is not blank, but other columns may have data in rows further down the sheet.

That's perfect, thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback