New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add Sheet.used_range property #112

Closed
fzumstein opened this Issue Nov 12, 2014 · 3 comments

Comments

Projects
None yet
3 participants
@fzumstein
Member

fzumstein commented Nov 12, 2014

No description provided.

@bklaas

This comment has been minimized.

bklaas commented Jan 12, 2015

I would have immediate use for a used_range method. Case in point, I deal with spreadsheets that have sparse tabular data. That is, not every cell in the relevant portion of the spreadsheet can be expected to have values in them. As a result, the horizontal and vertical methods are not reliable because they grab the first N cells before hitting a blank cell, then stop.

The workaround method I'm doing for this involves looking downward in the first column for an tag (which luckily is present, only because of our own conventions). I can effectively find the last used row in the spreadsheet this way, but it's by far the biggest bottleneck in the code I'm writing.

Word of warning: I've accessed the COM object's UsedRange field before in VBA, and I've never felt completely confident it can be trusted.

@fzumstein

This comment has been minimized.

Member

fzumstein commented Jan 12, 2015

There's a good chance it'll be in soon -- in the meantime, you can use the following workaround (assuming you are on Windows): Sheet('Sheet1').xl_sheet.UsedRange.GetAddress(), to get the address of the bottom right cell, for example. You're word of warning is correct - that's something you have to keep in mind...

@fzumstein fzumstein modified the milestones: v0.3.1, v0.3.2, v0.3.3 Jan 16, 2015

@fzumstein fzumstein modified the milestones: v0.3.3, v0.3.4 Feb 15, 2015

@fzumstein fzumstein modified the milestone: v0.3.4 Mar 9, 2015

@fzumstein fzumstein added this to the v0.3.7 milestone Jul 27, 2015

@fzumstein fzumstein modified the milestones: v0.3.7, v0.3.8 Sep 10, 2015

@fzumstein fzumstein modified the milestones: v0.4.1, v0.4.2 Sep 26, 2015

@fzumstein fzumstein modified the milestones: v0.4.2, v0.4.3 Oct 25, 2015

@fzumstein fzumstein modified the milestones: v0.6.2, v0.6.1 Dec 4, 2015

@fzumstein fzumstein modified the milestones: v0.6.2, v0.6.3, v0.6.4 Dec 14, 2015

@fzumstein fzumstein modified the milestones: v0.6.4, v0.6.5 Jan 6, 2016

@fzumstein fzumstein modified the milestones: v0.6.5, v0.6.6 Jan 22, 2016

@kudai

This comment has been minimized.

kudai commented Apr 1, 2016

Most of these approaches are pretty inefficient.

I found an excel formula that computes it - so I'm writing it to an unused cell in Excel and reading back the value. The difference for me was from 12 seconds to less than 0.1 seconds!
The formula is "=MATCH(REPT("z",255), [excel range]"

@fzumstein fzumstein modified the milestones: v0.7.2, short list Apr 2, 2016

@fzumstein fzumstein modified the milestones: short list, v0.12.2 Oct 15, 2018

@fzumstein fzumstein closed this in 25cc6bf Oct 15, 2018

fzumstein added a commit that referenced this issue Oct 15, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment