Click to Move Excel List Items Up or Down

Your challenge for today is to come up with a real-life use for this nifty (do the kids still say nifty?) Excel scrolling tool.

Here’s how the tool works:

  • Click a button to select a group of cells that you want to work with, like this list of months.
  • Then, click the up and down arrows, to scroll the list through the selected cells.
  • When you’re done, click the Exit button, to “release” the scroll area.

In this example, the scrolling list is in cells D8:E19 – months and numbers. The original list had January at the top, and February in the second row. I clicked the Up arrow twice, and now March is at the top of the list.


Cautions Before Using

In his sample file, Jim lists a few things to consider, before you use the code in one of your own files.

  1. The Custom Scroll Area program may alter...
    • Cell interior colors
    • Conditional Formatting
    • Data Validation
    • Formulas
    • Merged cells
  2. Undo (on the edit menu) will not undo custom scrolling.

Possible Uses

Jim Cone sent me this file, because he thought it was a unique tool, but he couldn’t think of a practical use for it. He wondered if an Excel slot machine was possible.

I don’t have any ideas, but maybe the Blue Jays could use it to rotate their starting lineup, now that baseball season is underway.

Do you have any suggestions, brilliant or otherwise?

Download the Sample File

To see the scrolling tool, and the code that runs it, you can download Jim’s sample file from my Contextures website. On the Sample Files page, go to the “UserForms, VBA, Add-Ins” section, and look for UF0021 – Scroll Items Up and Down in List

The VBA code is protected, and the password is: s

And if you have any ideas or suggestions, please share them in the comments, or contact Jim at the address in the sample file. Thanks!


You may also like...

9 Responses

  1. Abhijeet says:

    I could think of one use for custom scroll area for a list. Say we have created a data validation list of months from Jan to Dec. As months go by, one need to scroll down, particularly after seven months. With the help of custom scroll every month, one can scroll list to make current month as first.

    Second use could be in case the workbook is sent to many users who are required to select a department from a list of departments, each user can scroll department list to make its own department first.

  2. Darin Myers says:

    I use something similar in my reporting role. I have an Excel file that lists all the reports I need to run and send out. From this sheet, I can run data for the selected report or create an appropriate email with that report attached.

    When I have a report that I no longer need, I remove it from the list. I then want to move the list up to remove the blank space(s). The issue is that there are several data sheets where each report is listed in order to provide more information for the web-scrape or email procedures. To simplify my process, I added up and down arrows to the main page with some code attached. When I select a report or a range of reports, I can move the single item or the whole group up or down on all the sheets at once. Saved me tons of time.

  3. chip says:

    I have a practical use for this but on a personal basis not work-related. I send an email each week to a group of friends who gather for dinner and we take turns bringing various elements of the dinner. (We abandoned potluck after the dinner that consisted of 8 different people who all brought chips and salsa.) So I have Entree1, Entree2, Side1, Side2, Dessert, Bread, Drinks and a couple of families are “Off” each week because there’s no more food needed. I’ve always done a botch job of cutting and pasting, sometimes assigning the same people two items and leaving off families for weeks at a time because they accidentally got dropped from the rotation. So with this it’s a matter of keep the food items fixed and rotating the people.

  4. chip says:

    Now I just need to set up a sheet to accept the date reminders and other details and shoot out the emails. Hmmm…I’ve done that sort of thing with outlook but don’t use outlook for my personal email. But it perfectly suits my penchant for taking a simple manual process and spending hours researching and and creating an elaborate automated approach, so I’m in! Thanks for the head start.

  5. B says:

    I’m very intrigued by this spreadsheet. Unfortunately, it won’t run on my Mac with Office 2011. Can you recommend any tweaks to get this sheet to work for me.

Leave a Reply

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