How do I get Excel 2007 to scroll to a highlighted cell?

[JonWestcot]

Hi all:

I'm working on a bit of VBA code that is used to insert a variable number of rows into a spreadsheet at a particular point on the sheet. The insert is working just fine now (after I managed to figure out some -- to me -- bizarre behavior on Excel's part), except for one thing. The particular sheet on which the inserts occur has rows at the top of the sheet that are locked (i.e., non-scrolling). A cell below the locked rows must be selected before the insert can proceed. After the insert, the previously selected cell is Activated, but I also need to ensure that it always shows up on the screen.

Here's an example:

On my particular screen, it will display 50 rows. The top 10 rows are locked (via Range.FreezePanes). If I scroll down to row 70 and insert 5 rows (via my VBA code), the highlighted cell will make its way to row 75. However, after the code runs, the displayed rows are again 1 through 50, although the highlighted cell is correctly highlighted where it should be -- it just isn't VISIBLE.

According to the Help documentation, Range.Show is supposed to scroll the rows to ensure that the highlighted cell is visible on the screen. But it doesn't seem to work.

Any suggestions? Should I try to force a scrolling of the rows through VBA code? And how would I even DO that?

Thanks in advance!

Sincerely,

Jon

 

[VoG]

Try like this

Application.GoTo Range("A70"), scroll:=True

 

[JonWestcot]

Hi Peter:

Thanks! That works, but it causes the row to be placed at the very top of the scrollable rows. I guess that's what it's supposed to do, but I was hoping to get it to pretty much keep the same cell location on the screen before the insert was requested.

But, I think I figured out what the problem was... or, at least, what was causing Excel to display things from the beginning. I was unlocking the locked rows prior to the insert, then relocking them after it was done. Apparently, this caused Excel some consternation, and the screen showed me the sheet from the beginning.

Why was I doing this? Legacy code. This is a porting of code from Lotus 1-2-3's LotusScript, and that system apparently required the locked rows to be unlocked before an insert was performed. Excel does not seem to have that restriction. Commenting out the calls to the unlock/lock routines solved the problem exactly as I hoped it would work.

Thanks again for the quick answer. It works exactly as it should, but it just wasn't what I wanted it to do. Still, your answer showed me something I didn't know about VBA and the Excel object model, so it's all good! <g>

Sincerely,

Jon