How to find Visible Range in Excel window on a Screen

.

Dear Friends,

Its been a while I posted any post here. I am back with so many interesting stuff for you in Excel VBA. Here is a very small but useful excel vba tip for you.

This is about knowing the range which are currently visible to the user on his/her screen. Do not get confused with the visible or hidden cell here. This is not about that. This is more about how many rows and columns are visible of your excel window on different size of screens.

It is a very simple command in VBA which gives you the visible range on your screen.



ActiveWindow.visibleRange

Above statement returns an Object of Range Type.

From the above statement it is clear that VisibleRange is a method of ActiveWindow of an Excel. It is not related to a particular workbook but it works on ActiveWindow.

What is the use of this Method?

This is not a very common method to be used. But this is useful if you are trying to display some data in different format based on the visible range of your excel sheet for a different users having different size of monitors.

To Make a particular Cell or Range in Center of the Screen

To do so, I will have to identify the center of the screen because every screen might have different size.

Here the tricky part is identifying the Center of the screen Making a particular cell or cell range because center of the screen may vary for different size of screens. In this case, this method will help you making a cell range in the center of the screen.

To Scroll to a particular cell in Excel, we will use Application.GoTo method.

What is Application.GoTo Method and how it is used here?

Application.GoTo Method in Excel VBA takes input as a Range where you want to scroll and make that cell visible on the screen. This method always scroll the screen to make the cell at Top-Left corner cell on the screen. Here in this article I am not covering much about Application.GoTo method. Refer the below image:

GoTo Method

Application.GoTo Method

For Example, To make Cell to be visible in Center of the screen, if I use Application.GoTo Range(“Z16”), TRUE method then Z16 cell will be visible at the top left corner of the screen which is not my expectation. My expectation is to display Z16 cell always in center of my screen.
<  If I know how many rows and how many columns are visible on the screen, then by using the ActiveWindow.VisibleRange method, I can always find such address by using below formula which if kept at the top left corner then Z16 will remain in center.

 

Formula to find Top Left Row

= First Row Number of MyCell + (total row number of myCell Range)/2 – half of total number of rows visible on the screen

Formula to find Top Left Left Column

= Column Number of MyCell + (total Column number of myCell Range)/2 – half of total number of columns visible on the screen

Exception Scenarios

Consider a scenario where Row Number of My Cell is less than half of total number of rows visible on the screen. In such case I would like 1 as a Top left row number. Similarly for columns as well.

Solution:

To handle the above scenario you can take a max value between 1 and the difference which is being calculated like below

Formula to find Top Left Row

= MAX(1, First Row Number of MyCell + (total row number of myCell Range)/2 – half of total number of rows visible on the screen)

Formula to find Top Left Left Column

= MAX(1, Column Number of MyCell + (total Column number of myCell Range)/2 – half of total number of columns visible on the screen)

Using ActiveWindow.VisibleRange

method we can get the row and column count dynamically for each type of screens.


Sub MakeItInCenter(myCell As Range)
Dim VisibleRows
Dim VisibleColumns
Dim GoToRow
Dim GoToCol

VisibleRows = ActiveWindow.visibleRange.Rows.Count
VisibleColumns = ActiveWindow.visibleRange.Columns.Count

' To make the myCell as center, calculate the reference
' Cell address for Application.GoTo Method
' Plesae read the explanation provided on the
' LearnExcelMacro.com article.
GoToRow = Application.WorksheetFunction.Max _
(1, myCell.Row + (myCell.Rows.Count / 2) - (VisibleRows / 2))
GoToCol = Application.WorksheetFunction.Max _
(1, myCell.Column + (myCell.Columns.Count / 2) - (VisibleColumns / 2))
' Now use GoTo Method to scroll to the screen to make myCell visible in Center
Application.Goto Parent.Cells(GoToRow, GoToCol), True
End Sub

How to use above Procedure in your code

Step 1. Copy the above Procedure
Step 2. Go to your VBA Screen and Add a Module
Step 3.Paste this code
Step 4. Now call this function from anywhere in your program by using below statement:



Call MakeItInCenter (Range("Z16")

Thank you so much friends for reading… Hope this helps you in your day-to-day programming in VBA.

Soon I will be sharing few tools which I developed in this idle period.. till then have a nice time.. 🙂 Keep reading

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

2 Comments

  1. Tiana

    “Invalid settings file in foedlrwherethefileislocated.rmskin”.Could someone please help me with this so i can change my win$hit look to something more better? :)Thanks allready 🙂

    Reply
  2. harinder kumar

    can you teach me excel macro

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest