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:

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

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