Excel scrolling with vba

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:33 PM GMT

Question

QUESTION: I have an2007 program processing rows of data entered into a spreadsheet, which is run on different computers with various-sized screens. The command: ActiveSheet.ScrollRow=1 sets the start of data at the top of screen, but is there an equivalent command that will place the end of data (or any specified row number) at the bottom of screen? Alternatively, is there any way of reading programmatically the height of screen currently in use, so that by setting the row height, I can ensure that the user can see the latest entry, plus as much of the preceding data as possible? Terry

ANSWER: Terry


Range("A1:K45").Select ActiveWindow.Zoom = True

will get that area within the activewindow. It does it by zooming, so it isn't perfect, but it should do what you want. You will have to experiment to get best results.



---------- FOLLOW-UP ----------

QUESTION: Thank you for your prompt reply to my 'scrolling from bottom of screen' question. To apply the Zoom property effectively I still need to know screen height, to make sure it doesn't distort the cells and form controls too much. Given this, I will stick to the scroll method and customise (I know you spell it with a 'z', but I'm Australian!) the VBA code based on the number of lines visible on screen. However, your 'Zoom' solution will suit another spreadsheet I have developed, which converts field-observed coordinates to screen dimensions (in points) to plot shapes of surveyed blocks of land, buildings or other 3D structures. Large plots tend to lose some of their detail, so your suggestion will allow me to write code to zoom in on the more intricate areas. I guess this is not really a follow-up question, but an explanation and thank-you all in one. I am a novice at VBA so you will no doubt be hearing from me again. Terry

Answer

Terry,

I might point out on that URL I sent, that if you hit the print link you will get the whole chapter from that book (about 27 pages). This is a very good book written by a couple of the leading authorities ondevelopment. John Green is from Australia as well. (Bullen from Great Britain and Bovey from the USA).

Here is the link directly:

https://www.informit.com/articles/printerfriendly.aspx?p=366892

Advertisement

©2017 eLuminary LLC. All rights reserved.