 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
In Excel 2007-2016 we have an option in the Ribbon on the Insert tab named Table. This option also exists in Excel 2003 but with the name List and it's found under the Data menu in Excel 2003.
If you select a cell in your normal range and press the Table button it will show you this dialog. In Excel 2003 you can right click on a cell in your range and choose "Create List" or use Data>List>Create List in the menu.
There are many benefits when you use Tables instead of a normal range in
your worksheet. After you defined the Table in Excel 2007-2016 you will have
access to a Ribbon tab named Table Tools with:
Table Styles gallery
for different color schemes(with live preview)
Add a total row with one
click
Option to remove duplicates
External data options
A few
other benefits if your data is in a Table in Excel 2007-2016 are:
No
problem if there is a empty row or column when your filter your Table. It
will automatic turn on AutoFilter in the Table so you can use the AutoFilter
dropdowns to sort or Filter. When you scroll past the column headers, the
headers at the top of screen will automatically switch to display the header
names. If you add a record below the last row in the Table or add a column
to the right or left then the Table will automatically expend to include the new
row or column. Charts or Pivot Tables based on a Table will automatically update
if your Table size changes. If you enter a formula in the first cell of the
Table row it will automatically fill down the whole field. If you edit one
formula in a field all formulas in that field will update automatically.
Note: Most benefits are new in Excel 2007-2016 and not
available in List in Excel 2003.
On this page you can download a example workbook for Excel 2003 (List)
and one for Excel 2007-2016(Table) with a few code examples. All macros in
the example workbook are working without changing the code. Some code will
need to be edited if you use it in your own files, but the changes shouldn’t
be too difficult.
Note: Read the information good
on the worksheets and also the comments in the code.
Download 2007-2016 example workbook
Download 2003 example workbook
Examples in the files :
1) Copy visible data from
the Table/List to a new worksheet
2) Copy visible data
from the Table/List to a new workbook
3) Filter in Place
with VBA code
4) Filter and copy the visible data to a
new worksheet
5) Filter and copy the visible data to a
new workbook
6) Copy the data for every unique value to
own worksheet
7) Copy the data for every unique value to
own workbook
Check out also my Table Tools add-in
for Excel 2007-2016.
You can find most macro options and a lot more in
this add-in.