How to get Min / Max Row / Col in a non-contiguous range?

[superlative]

Right now I define it by rowing through each cell:

Please Login or Register  to view this content.

and I achieve the results I want, but it seems to me to be a very inefficient way to do it. I don't want to row through millions of cells just to find the edges of this range!!!!

There must be a better way? Thanks.

[superlative]

I've also tried:

Please Login or Register  to view this content.

but this only works if the user selects continuous rows/columns

[romperstomper]

If you want the highest row number, loop through the Areas in the selection and check the row number of the last cell in each one, storing the max value.

[Kaper]

To extend Rory's answer a bit: try the following code in an empty sheet:

Please Login or Register  to view this content.

And now you can drop all the displaying-related parts of the code and use it for your somerange

[Akuini]

Just another way:

Please Login or Register  to view this content.

[Marc L]

There must be a better way?

Mayve via the Range.Find method …

[Kaper]

Hi Marc,

I think that Range.Find will look on whole (potentially not continous) range and locate specihic values (What:=) so I'm not sure how it could be used to find "smalles surrounding rectangle" address - I think this is what superlative looks for.

PS. My code from above with removed side/demonstration parts seems to be still competitive compared to range.address and address splitting method:

Please Login or Register  to view this content.

[Marc L]

I was thinking for cells containing data instead of an easy last column # from a range for example …

[superlative]

Hi everyone,

thanks for all the great ideas!! I actually managed to find some code online very similar to kaper's and modified it according to my needs. Here it is:

Please Login or Register  to view this content.

using WorksheetFunction.Min appears cleaner, but is there a performance benefit? Anyone know? Thanks!

[superlative]

Akuini: do you think your code is faster? I use this function extensively so I need it to be fast. Thanks!

[Akuini]

Akuini: do you think your code is faster? I use this function extensively so I need it to be fast. Thanks!

Well, you need to test it, you can try putting the codes in say 10K loops then see how long it takes, I'm guessing mine is a bit faster but I think the difference won't be noticeable in practice.
I changed my code a bit & put it in 10K loops & used a range with more areas, it took 1.28125 seconds.

Please Login or Register  to view this content.

The result:
rows: 3:22
columns: 2:10
1.28125

Test your code by using the same set up, see how it goes.

[Akuini]

I forgot something, using range address has a limit, the string address can't be longer than 255 characters, otherwise it will raise an error.
So I think your code or Kaper's code is better.

But actually how do you get the range? By selecting the range manually?

[romperstomper]

using WorksheetFunction.Min appears cleaner, but is there a performance benefit? Anyone know? Thanks!

I would expect a call to Worksheetfunction to be slower, but you should test it.