Intersect Not Working with my Last Cell

[Guzzlr]

Hello
For Finding the last cell with data, I have:

Dim LastCell as Range
Set LastCell = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
MsgBox "The Last Cell with Data Is In Row:  " & LastCell.Row

The above is working correctly.


I now have the code that I am trying to incorporate LastCell into:

Set fnd = Range("5:5").Find("Service Provider", , , xlWhole, , , False, , False)
If Not fnd Is Nothing Then
    Intersect(Range("5:LastCell"), fnd.EntireColumn).Copy
    Range("O5").Insert Shift:=xlToRight
        Intersect(Range("1:LastCell"), fnd.EntireColumn).Clear
        Range("B:B").Delete

The above code I have used many times, but I've always used something like: Range ("5:5000")
I would like to be able to use the LastCell instead of a random number like Row 5000. However, I've never used the LastCell before to replace the row number.
How would I do that?
Thank you for the help

 

[Joe4]

Everything inside double-quotes is treated as literal text.
All variables must be OUTSIDE of the double-quotes.
And you want to pull the Row off of the cell it finds.

So this:

Range("5:LastCell")

should be this:

Range("5:" & LastCell.Row)

 

[Jeffrey Mahoney]

Does this help?

Sub Test()
  Dim fnd As Range
  Set fnd = Range("5:5").Find("Service Provider", , , xlWhole, , , False, , False)
  If Not fnd Is Nothing Then
    Intersect(Range("5:" & Cells.Rows.Count), fnd.EntireColumn).Copy
    Range("O5").Insert Shift:=xlToRight
    Intersect(Range("5:" & Cells.Rows.Count), fnd.EntireColumn).Clear
    Range("B:B").Delete
  End If
End Sub

 

[Guzzlr]

Everything inside double-quotes is treated as literal text.
All variables must be OUTSIDE of the double-quotes.
And you want to pull the Row off of the cell it finds.

So this:

Range("5:LastCell")

should be this:

Range("5:" & LastCell.Row)

Oh...thanks for the schooling!