Limit to Last row with data

[abenitez77]

I use this line below to get the last row with data and it works fine. In a search, the last row came back with 1,048,532. It did have some text in a cell, so it worked properly. How can I limit the search so that it does not come back with a row greater than 10,000 ?

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

 

[MARK858]

Can't you just use an if statement?

lastrow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If lastrow > 10000 Then lastrow = 10000

 

[Rick Rothstein]

I use this line below to get the last row with data and it works fine. In a search, the last row came back with 1,048,532. It did have some text in a cell, so it worked properly. How can I limit the search so that it does not come back with a row greater than 10,000 ?

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

Try...

LastRow = Rows("1:10000").Find("*", , xlValues, , xlRows, xlPrevious).Row

 

[abenitez77]

Can't you just use an if statement?

lastrow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If lastrow > 10000 Then lastrow = 10000

lol...sometimes the simple solution stares you in the face and you just can't see it because you are distracted with a complex solution.

thanks!

 

[MARK858]

You're welcome but if you actually want to restrict the range the search is being carried out on see Rick's answer.