AL_XResearch
asked on
Excel VBA : Get range currently displayed on sheet
I am using a bit of code to save the current worksheet as an image to disk. This works perfectly except that I have used the following to get the range that is currently displayed (i.e. not just the visible cells) :
This does return a valid range however my sheet has rows and columns frozen and the above command only returns a range of the scrollable cells.
I want to find some way to get the visible range and include the frozen cells.
Thanks very much.
activewindow.VisibleRange.Address
This does return a valid range however my sheet has rows and columns frozen and the above command only returns a range of the scrollable cells.
I want to find some way to get the visible range and include the frozen cells.
Thanks very much.
ASKER
Saurabh: Thanks for the quick response but that would not get me the current viewable range (cells currently shown only) - it would get me all visible cells on the sheet - not the same thing at all.
Incidentally it is a good idea to keep away from 'UsedRange' as it is unreliable and requires regular 'resetting'.
Incidentally it is a good idea to keep away from 'UsedRange' as it is unreliable and requires regular 'resetting'.
ASKER
Alternatively can someone supply an example of how to get a screenshot (like PrintScreen) without including the ribbon of formula bar ?
Ideally without a huge module and dozens of API declarations (as are nearly all the screenshot solutions I see on the web).
Ideally without a huge module and dozens of API declarations (as are nearly all the screenshot solutions I see on the web).
I think MsgBox ActiveSheet.UsedRange.Addr ess will give you the address of used range irrespective of filters applied.
AL_XResearch,
Usedrange feature i agree require regular resetting in the earlier version of excel i.e. 2003 ..But i haven't see that kind of issue in 2007,10 and 13 and i have seen it getting resetted properly..
Also the special cells will pick up the data correctly since freeze panes don't hide the row it just limit your access of what you can see or not..so the above code will give you answer correctly about what you are looking for...
Saurabh...
Usedrange feature i agree require regular resetting in the earlier version of excel i.e. 2003 ..But i haven't see that kind of issue in 2007,10 and 13 and i have seen it getting resetted properly..
Also the special cells will pick up the data correctly since freeze panes don't hide the row it just limit your access of what you can see or not..so the above code will give you answer correctly about what you are looking for...
Saurabh...
ASKER
sktneer: Yes it will - which is not the question.
I want to be able to get either:
I want to be able to get either:
the address of the viewable cells including the frozen rows and columns
a code sample that will allow me to get a screenshot of the active worksheet without the ribbon or taskbar
ASKER
Saurabh: Well I can't comment on UsedRange stability improvements in 2007 onwards but generally as a rule I avoid it as MS rarely fix these kind of in-grained Excel bugs / design issues and a correct answer is guaranteed if you roll-your-own (and not complicated either).
That is all off-topic anyhow since as I say UsedRange is not correct for what I want. I need to only get what is displayed in the screen - not all the cells that currently have a value and are visible.
Depending on the window's zoom factor the window may only show 27 columns of a 400 column sheet. I want to get the 27 columns and not all 400 otherwise the screenshot would be impossible to see !
That is all off-topic anyhow since as I say UsedRange is not correct for what I want. I need to only get what is displayed in the screen - not all the cells that currently have a value and are visible.
Depending on the window's zoom factor the window may only show 27 columns of a 400 column sheet. I want to get the 27 columns and not all 400 otherwise the screenshot would be impossible to see !
I see where is your problem..When freeze the panes basically in excel it splits the window so it won't consider as a part of the same view which you see in the screen..Alternatively you can do something like this..unfreeze and capture and then freeze again...
ActiveWindow.FreezePanes = False
MsgBox ActiveWindow.VisibleRange.Address
Rows("2:2").Select
ActiveWindow.FreezePanes = True
ASKER
Yea I though of the same thing - however that will show all the columns which are beyond the frozen columns which have been scrolled out-of-view.
An example of normal usage of my workbook:
I therefore need a way to copy the frozen cells and the currently viewable cells of the non-frozen range.
I am using CopyPicture to copy the cells to a chart object and using it's Export function to save to a JPG file. It is fast, reliable, gives a good quality picture and the code is small - the only issue is Chart.Export doesn't support multi-area ranges. It does honor hidden rows & columns but if I use some custom code to extend the 'ActiveWindow.VisibleRange ' range to include the frozen columns then is also includes the cells that are visible but have been scrolled out-of-view.
An example of normal usage of my workbook:
Columns A to V hidden
Rows 1 to 34 hidden
Rows and Columns frozen on cell Z34
Sheet scrolled so that the viewable range is BE38: BW70
The rows & columns that are 'scrollable' (i.e. not frozen) also include hidden rows & columns - decided by the user ribbon selections at runtime which cannot be predicted. To take columns as an example the structure could be : Hidden, Visible, Hidden, Visible ......., Hidden. Visible.
I therefore need a way to copy the frozen cells and the currently viewable cells of the non-frozen range.
I am using CopyPicture to copy the cells to a chart object and using it's Export function to save to a JPG file. It is fast, reliable, gives a good quality picture and the code is small - the only issue is Chart.Export doesn't support multi-area ranges. It does honor hidden rows & columns but if I use some custom code to extend the 'ActiveWindow.VisibleRange
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, as I said (think we sent two posts in at the same time), the 'Chart.Export' will honor hidden cells - I need someway of not copying cells that have been scrolled out-of-view but are still 'visible' in the range sense.
Can we transfer data from here to new workbook..then creating a chart object from that?? Because unfortunately what you are looking for is an excel limitation and their is nothing much you can do for the same..
ASKER
Hmmm ... I had though about copying data the frozen columns, frozen rows and 'viewable' range then pasting them together to make a 'fake sheet' and then copying that but it would be extremely complicated (due to the complex structure I have outlined) and I was hoping to avoid it.
I had suspected it was an Excel limitation / design-flaw but was hoping someone else had hit on a clever idea.
I had suspected it was an Excel limitation / design-flaw but was hoping someone else had hit on a clever idea.
ASKER
The other (and simpler) way would be to perform a selective-print-screen to only copy the cells. I have no idea how to target a screenshot at a specific section of the screen via the API - of even how to get the handle to the 'worksheet area' child window.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I've requested that this question be closed as follows:
Accepted answer: 0 points for AL_XResearch's comment #a40958536
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 0 points for AL_XResearch's comment #a40958536
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
I have tried to close this question several times now and it never takes. Please advise how to resolve this.
ASKER
Saurabh's solution gave me the key concept I needed and my final function (marked as an answer) provided the best solution- even though it doesn't completely resolve the issue
Open in new window
Saurabh...