If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
Below are excerpts from explanatory notes I posted in an example workbook for Tutorial 03 on Navigating within Spreadsheets + Manipulating Tabulated Data Using VBA Code – under the them: use of NAMED RANGES in MS Excel for Super Spreadsheet Optimisation and Automation | www.excelheaven.biz
This time around, in addition to going further to show a more advanced use of range names in a complex formula, I also show how to use them in VBA code to manipulate large spreadsheet data ranges at once.
This range (D9: D503) is named “nfItemsSold”. I’ve hidden rows 40 to 503 to fit the table into the print-ready format for this tutorial. To display the full table, click on the row header labeled 39 and drag two or more steps downwards to highlight row header labeled 504 or higher, then right-click and cilick UNHIDE on the shortcut menu that appears. Th efull table will be displayed. Note that clicking ANY of the ERASE buttons above will cause ALL entries in the entrie table length to be erased, regardless of whether the rows are hidden or not

Below: Note how the autocomplete feature is activated as soon as I type the period after the range name definition. In other words, Excel recognises this defined name as an object and readily supplied “properties” for me to choose from. I choose “C learContents” which achieves the effect of erasing the contents of the specified range.


I added the last 2 VBA subroutines to enable you see the ERASE code working while the table is fully displayed and also while part of it is hidden. You will find this kind of functiona;ity useful in handling large spreadsheets.
TIP: In addition to the notes and screenshots shown here, my Excel Heaven club members receive the example workbooks with unprotected cells and code modules, so they can study them and modify to test their own ideas. Click here to request a copy for your own use.
Posted on 16 October 2016
Tags: Case Study, Navigating within Spreadsheets + Manipulating Tabulated Data Using VBA Code, Tutorial 03
About the Author
Tayo K. Solagbade is a Location Independent Performance Improvement Specialist and Multipreneur (i.e. a highly versatile/multi-skilled entrepreneur), with a bias for delivering Best Practice solutions to Farm Businesses and others.
Since 2002, he has earned multiple streams of income providing individuals and organisations with personal development training and coaching, custom MS Excel-VB solutions, web marketing systems, freelance writing services, and best practice extension support services (for farm business owners).
Tayo is the author of the Self-Development (SD) Bible™, the popular Livestock Feed Formulation Handbook, and developer of its accompanying Excel-VB driven Ration Formulator - as well as the increasingly popular Monthly Poultry Farm Manager app.
On 1st April 2013, Tayo (who reads, writes and speaks the French language) relocated to Cotonou, in the French Speaking Benin Republic on the first lap of his slow travels across the West African region. His key purpose is to deliver talks, seminars and workshops on his key areas of focus and interest to interested audiences (Email tayo at tksola dot com for details).
When he's not amazing clients with his superhuman skills (wink), Tayo works as the creative force behind his Daily Self-Development Nuggets blog - on which he also publishes previews of paid issues of The Farm CEO™ Newspaper (www.thefarmceo.net), in addition to his FREE Weekly Public Speaking/Web Marketing IDEAS newsletter - which he uses to promote Burt Dubin's Public Speaking Mentoring service to experts across the African continent.
Visit Tayo's Flagship Performance Improvement website to download over 10 performance improvement resources to boost your personal and work related productivity.
Join Tayo's international community of fans on his Flagship MS Excel Heaven Facebook page (click here). You can also connect with him via Twitter (@tksola).
Warning: count(): Parameter must be an array or an object that implements Countable in /home1/tayoswdg/public_html/sdnuggets/wp-includes/class-wp-comment-query.php on line 399