This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

The hidden door to extra functions in Excel Part five of a six-part series of blogs
Did you know that you could combine range names and legacy Excel 4 functions to list out the worksheets or range names in your workbook - wthout using macros? I didn't either - here's how to do this, and much more! The hidden door to extra functions in Excel A little background Using Excel 4 functions with range names Example - Listing Worksheets Other ideas for the use of Excel 4 functions (this blog) References for this blog

The hidden door to extra functions in Excel

Part five of a six-part series of blogs

Did you know that you could combine range names and legacy Excel 4 functions to list out the worksheets or range names in your workbook - wthout using macros? I didn't either - here's how to do this, and much more!

The hidden door to extra functions in Excel
A little background
Using Excel 4 functions with range names
Example - Listing Worksheets
Other ideas for the use of Excel 4 functions (this blog)
References for this blog

Posted by Andy Brown on 15 September 2022

In this blog

Listing out the range names in a workbook
Showing the last active cell

Other ideas for the use of Excel 4 functions

Below are some of the other ideas I could come up with - see how many more you can think of!

Listing out the range names in a workbook

To list out the range names in a workbook, first create a range name using the Names() function:

This range name will return a list of the range names in this workbook.

Then use this, transposing the results:

The list of range names in the current workbook.

You could even use an additional argument to the Names() function to apply a wildcard filter (for example to show all of the range names containing the letters owl).

Showing the last active cell

You could use the Get.Document function to get the row and column number of the last active cell in a worksheet. First create a range name for the last row, using the argument 10:

See the last blog in this series for how to find out the argument numbers and function names that you can use.

Then do the same for the last active column:

This range name gives the last active column, using the argument 12.

You can now put these together to get the cell reference of the last active cell:

Get the address of the cell with the given row and column number (the argument 4 returns it as a relative reference, not an absolute one).

Parts of this blog
The hidden door to extra functions in Excel A little background Using Excel 4 functions with range names Example - Listing Worksheets Other ideas for the use of Excel 4 functions (this blog) References for this blog