[VBA] Add rows without scrolling the worksheet?

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a concept I'm considering, and I'm turning to the kind and skilled pros here to entighten me as to whether my idea is remoely possible. I fear the biggest challenge will be trying to describe things.

I have a worksheet that I use as a form for users to edit and manipulate data through a series of list cell validations and macros blah blah blah. The interactive part of the form is essentially the upper half of my display, say rows 1 - 24. Rows 24-45 accumulate data in rows. With each user data entry in the upper half, a row is added in the range of row 24-45.

When data is added beyond row 45, the worksheet naturally starts scrolling up. When this happens, the interactive part of the worksheet starts to get lost.

So, is it possible to add rows and not scroll the worksheet as they are added? The kicker, is I still want the user to be able to review the rows of data beyond row 24. If I am able to add rows without ther window scrolling, I would need some means of allowing the user to be able to access the rows out of view, but again, without scrolling the interactive portion off the visible area.

Before I invest too much time figuring out how to do it, I hope to avoid that waste by being told whether or not its possible at all.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
You can use freeze panes, to keep the top part of your sheet visible.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,339
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is there a reason you aren't using an actual userform?
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You can use freeze panes, to keep the top part of your sheet visible.
I can certainly try that to see if it provides what I'm looking for. Thanks.

Is there a reason you aren't using an actual userform?

Hey Rory. That is a fair question. I have been working on this "application" for close to five years now (just look at my history here, those questions are for the most part associated with the same project). I've bounced between userforms, and worksheet based forms on a few occasions. Each one has it's merits and efficiencies, but also their challenges and limitations. My last kick at the can using userforms just got so complicated as I was bouncing information between workbooks, worksheets, and other user forms I abandoned that approach. Now, it's not necessarily the fault of Excel as much as perhaps poor planning on my part, but I have learned in so many ways that there are so many ways to do the same thing in Excel, that either way could provide satisfying results. I'm not looking for efficiency right now, as much as functionality, so to view my code based on my novice knowledge, you'd chuckle (and be embarassed). But, it's working (for the most part) LOL.

I appreciate everyone's kind help here. I find the one one one mentoring to be far more productive to me than trying to interpret and adapting all the different instructional sources out there. When I try to adapt a simple concept found via a Google search, it sometimes causes more problems than good.
 

Forum statistics

Threads
1,176,086
Messages
5,901,305
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top