Paging/ sorting in Range excel VBA like datagrid

Posted on 2014-04-27
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 70

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 70

Expert Comment

ID: 40026300
Very simple example code is provided in this workbook. There are a lot of different ways to tackle it, however.
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.


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 70

Expert Comment

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

Accepted Solution

Qlemo earned 300 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

752 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