Jealousy

[Gerald Higgins]

...I do try to consider all worlds before I respond to question.

I used to do that too, but it was taking me ages, and eventually I decided to simply assume that Excel works the same on Mars and Jupiter as it does on Earth.


But if anyone wants to hijack this thread into a whole new orbit, feel free to explain why it might be different . . . .

 

[RoryA]

Maybe it's a gravity thing - does End(xlUp) go further on the Moon than on Mars?

 

[mole999]

Maybe it's a gravity thing - does End(xlUp) go further on the Moon than on Mars?

It did do in 2007

 

[RoryA]

Depends where you started from...

 

[DonkeyOte]

Working with Dates could prove to be something of an issue anywhere other than Earth...

 

[mole999]

Measure in Earth standard days, obviously the rotational period of any other place would knacker a watch as Arthur Dent found out

 

[Oorang]

rofl @ End(xlUp) * Gravity

That sort of made me think of another facet in this whole conversation. Sledgehammers (another one I am "occasionally" guilty of) I once saw (participated in) a whole thread on the "best" way to get the botton row. (It's this by the way: )

Private Function GetBottomRow(ByVal sh As Worksheet) As Long       
        GetBottomRow = sh.UsedRange(sh.UsedRange.Count).Row 
End Function

 

[RoryA]

I would disagree that that is the best way, though I am open to argument. Much time and energy has been spent overcoming Excel's occasional delusions as to what the UsedRange actually is!

 

[cornflakegirl]

Oorang - doesn't that find the lowest row with data or formatting, rather than the lowest row with data?

 

[Oorang]

What started the whole discussion was the used of Range("A65536").End(xlUp) could miss data in excel 2007 (if there are more than 65536 rows), but if you used a larger number then it wouldn't be cross compatible with earlier versions. Yes, if you have "dirty" data then usedrange can grab cells with no data... But I submit that any worksheet not having clean usedrange, is probably also not clean enough for automation without scrubbing anyway.
And for the purpose of the scrub itself you can always do a reasonably quick test of LenB(cll.Value) to shrink the range. The chief advantage being that usedrange will always get all of the data, where end(xlup) can miss data, both because of versioning problems, or because of presumption that you are using the longest column. If your data in not fully normalized (as may be the case in a scrub) then you might zoom right up past data by using too short of a column. The general principle being it was deemed better to have all the data + non-data cells, then miss data completely.