Solved

Paging/ sorting in Range excel VBA like datagrid

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

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
Very simple example code is provided in this workbook. There are a lot of different ways to tackle it, however.
EmployeeTable.xlsm
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:satmisha
Comment Utility
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 68

Expert Comment

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

Accepted Solution

by:
Qlemo earned 300 total points
Comment Utility
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
Comment Utility
Thanks a lot Pal for your wonderful answers.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now