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


Open in new window

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can use something like this...

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

Open in new window

AL_XResearchAuthor Commented:
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'.
AL_XResearchAuthor Commented:
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).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think MsgBox ActiveSheet.UsedRange.Address will give you the address of used range irrespective of filters applied.
Saurabh Singh TeotiaCommented:

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

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

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
AL_XResearchAuthor Commented:
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 !
Saurabh Singh TeotiaCommented:
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
    ActiveWindow.FreezePanes = True

Open in new window

AL_XResearchAuthor Commented:
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:
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' range to include the frozen columns then is also includes the cells that are visible but have been scrolled out-of-view.
Saurabh Singh TeotiaCommented:
Use this with a combination of special cell..That way it will ignore your hidden rows...

 ActiveWindow.FreezePanes = False
    MsgBox ActiveWindow.VisibleRange.SpecialCells(xlCellTypeVisible).Address
    ActiveWindow.FreezePanes = True

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AL_XResearchAuthor Commented:
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.
Saurabh Singh TeotiaCommented:
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..
AL_XResearchAuthor Commented:
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.
AL_XResearchAuthor Commented:
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.
AL_XResearchAuthor Commented:
In the end I created the below function which gets the bottom-right cell from the 'Viewable' range and the 1st visible top-left cell of the worksheet and then created a range between the two.

Function rngGetCurrentSheetViewableRange() As Range

    Dim rngScrollableCells As Range
    Dim rngBottomRightCell As Range
    Dim wksActiveSheet As Worksheet
    Set wksActiveSheet = ActiveSheet
    Set rngScrollableCells = ActiveWindow.VisibleRange
    With rngScrollableCells
        Set rngBottomRightCell = .Cells(.Cells.Count)
    End With

    Set rngGetCurrentSheetViewableRange = wksActiveSheet.Range("A1:" & rngBottomRightCell.Address)

End Function

Open in new window

True this does mean that you include all the 'unhidden' cells in between (even if they are not viewable) but I have just accepted this for the moment.
Martin LissOlder than dirtCommented:
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.
AL_XResearchAuthor Commented:
I have tried to close this question several times now and it never takes. Please advise how to resolve this.
AL_XResearchAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.