Last row combined with Current Region VBA

[Tigerexcel]

I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data that has data in all columns so it is possible that the quoted formula may fail if the the last row does not contain data in column D so I may have to find a more foolproof formula that will work in all situations.
The bigger issue that I have is I'd like to somehow link the Lastrow with the CurrentRegion property so that once the last row has been identified I can effectively work on that data range. I've found that something like DataRange = Lastrow.CurrentRegion doesn't work where DataRange is the table data. How can I write this in VBA with a minimum of coding?

 

[CephasOz]

No, of course it doesn't, @footoo.

You said "He was looking for the last used row in a specific range, not the whole sheet." and I was pointing out that that wasn't so, because if the last row for a specific range was required, then the simple code that I gave would do that (for a single-area range). So, in that part, I wasn't answering the initial post, I was responding to what you had incorrectly stated about it being for a specific range, which it wasn't.

Then, I went on to point out the part of the initial post that I was considering in my previous post, and for which my comments were pertinent.

 

[footoo]

Don't get your knickers in a twist. I doubt anyone's interested in our bickering and the "he said/you said" stuff.

 

[Tigerexcel]

Thanks CephasOz for chiming in, I love the idea of bulletproof range addressing. With the lockdown I'll be receiving plenty of spreadsheets from others in all sorts of formats ie the ranges are inconsistent and won't be able to simply walk over to a desk and seek clarity. I have been caught out before with ranges that had blank rows or hidden columns or ranges that started at cell C4 rather than A1. I had thought that if I combined the last row with the current region I might make this bulletproof but i've now seen that it might not get across every potential situation.

 

[footoo]

Tigerexcel
Why not have a look at the links in post #2 - you will find the same function there.

 

[CephasOz]

Hi *Tigerexcel. Glad it was useful.