struggling with lastrow formula

I found a few formulas online to find the lastrow of data in a workbook. The problem is I have pre-formatted 1500 rows to wrap text, borders, etc... so even though everything after row 100 is blank, the lastrow formulas I found online are returning 1500 as the lastrow instead of 100.

The following formulas return 1500 as the last row:
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
LastRow = ActiveSheet.UsedRange.Rows.Count

I found another formula online which seems to work in a test workbook I created, but in my workbook I'm getting an error:

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

The error I get is "Object variable or With block variable not set". I do have it in a with statement though so I'm not sure why it's not working.

Thanks for the feedback, but the worksheet is not empty. There must be another reason for this error occuring.

There are other activities going on in this workbook, but to replicate the error do the following:
In the Main sheet click on the "Customer Search" button. Then hit the Run button in the Customer Search sheet and the debug prompt should appear.

Keep in mind that I haven't written the part of code which will utilize this lastrow value.

I appreciate the help.

Change the line to:

Please Login or Register  to view this content.

Adding the "." before Cells tells the code to search in Sheet "Opportunities" per the line above it (With Sheets("Opportunities")

Let me know if this works.

Jakobshavn and BigBas, it works like a charm! Thank you both for helping me out with this.