Excel VBA : Get range currently displayed on sheet

You can use something like this...

MsgBox Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible).Address

Select allOpen in new window

Saurabh...

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'.

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).

I think MsgBox ActiveSheet.UsedRange.Address 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...

sktneer: Yes it will - which is not the question.

I want to be able to get either:

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 !

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

Select allOpen in new window

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.

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..

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.

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.

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.

I have tried to close this question several times now and it never takes. Please advise how to resolve this.

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