?
Solved

Paging/ sorting in Range excel VBA like datagrid

Posted on 2014-04-27
7
Medium Priority
?
583 Views
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.
0
Comment
Question by:satmisha
  • 4
  • 3
7 Comments
 
LVL 72

Expert Comment

by:Qlemo
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.
0
 

Author Comment

by:satmisha
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.
EmployeeTable.xlsx
0
 
LVL 72

Expert Comment

by:Qlemo
ID: 40026300
Very simple example code is provided in this workbook. There are a lot of different ways to tackle it, however.
EmployeeTable.xlsm
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:satmisha
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.
0
 
LVL 72

Expert Comment

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

Accepted Solution

by:
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.
EmployeeTable.xlsm
0
 

Author Closing Comment

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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

579 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