Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Paging/ sorting in Range excel VBA like datagrid

Posted on 2014-04-27
Medium Priority
Last Modified: 2014-04-28
Hi Expert,

I am looking datagrid features like paging and sorting in excel worksheet using VBA. Is it possible.

Currently I am using 1000(Rows) * 10(columns) matrix range where I am adding/deleting rows dynamically.

Is there any possibility to put paging/sorting inside that. for example at a time user can see only 100 rows of data. if user wants to see 101 row then he can simply click on next button and see 101 to 200 rows.

Hopefully looking forward to hearing from you.
Question by:satmisha
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 71

Expert Comment

ID: 40025915
Not by default, but you can "project" data of another sheet into a "windowed view".

For example, use A1 for keeping the page number, starting by 1.
Set an area starting at A2 to e.g. F11 to the formula:
  = Offset ( Data!2:2; ($A$1-1)*10; 0)
referring to a sheet "Data" containing the original matrix starting at row 2 (column headers in row 1). All you have to do is to increment A1 by a button or whatever. A1 could also sit on a different sheet, if required - doesn't matter at all.

Author Comment

ID: 40026243
Thanks for your reply. I would appreciate if you could provide some sample, I am totally new to this.

Enclosing sample excel file contains employee data. consist of 100+ rows. Can I make paging/sorting here through VBA/formula like you pointed out ?

Looking forward to hearing from you.
LVL 71

Expert Comment

ID: 40026300
Very simple example code is provided in this workbook. There are a lot of different ways to tackle it, however.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 40026620
I really fall in love with sorting the way you did it. :-)

On paging front cant we make other way (like we do search in google), on bottom we do get navigation buttons like previous, next ( Can be numbers as well like 1,2,3,4,5.... etc)  and when we click on them we can navigate in our records.

Suppose we have 100 records , at a time only 10 records user can see and if he wants to see 11th record he could click on next button and see records from 11 to 20 and so on. similarly if he wants to see previous records he could navigate accordingly.
LVL 71

Expert Comment

ID: 40026689
Using two buttons for paging isn't difficult. Building a list of page numbers needs more effort
LVL 71

Accepted Solution

Qlemo earned 1200 total points
ID: 40026966
Here the sheet using buttons for paging. Note that I have also used names for the current and max page numbers now. The result would need some formatting, e.g. to suppress empty results, and look more like the original table, of course.

Author Closing Comment

ID: 40028316
Thanks a lot Pal for your wonderful answers.

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question