Excel VBA Code - Get outline level of current row

Andrew O'Connor
Relken Engineering
Have a question or want to speak with Andrew O'Connor ? Contact us with your details.

Get the outline level of each rowDescription

This Excel VBA Code gets the outline level of each row.  This can be useful when determining the level of each row within the asset structure, FMECA, organisation, etc.. It can also be used to conduct advanced conditional formatting for each row (and is more stable than the inbuilt automatic styling for outline rows). To read about using outline levels in MS Excel to easily view and manage structured data, see Excel Tip: Collapsing rows with structured data.       

Features

This VBA code simply returns the Outline Level of each row.   If the user changes the row outline level, the formula will not automatically update.  Instead the user will need to press Ctrl-Alt-F9 to recalculate the workbook.

Use and Example

Simply type =Level() into the desired cells. To update values after any changes press Ctrl-Alt-F9.

Get the outline level from each row               

Related Functions

The following articles are related to this function:

File Attachment: 
PreviewAttachmentSize
Relken Excel Tip - Outline Levels_0.xlsm98 KB
  1. Function Level(Optional cCell As Range)
  2. ' LEVEL returns the outline level of the current row. It will not automatically update and therefore
  3. ' a recalculation Ctrl-Alt-F9 is required.
  4. '
  5. 'SYNTAX
  6. ' =level()
  7.  
  8. 'EXAMPLE
  9. ' Let the outline level of the row be 1, =level() returns 1.
  10. '
  11. ' Author: Andrew O'Connor <andrew.oconnor@relken.com>
  12. ' Date: 23 Apr 2013
  13. ' Copyright: 2014 Relken Engineering
  14.  
  15. If cCell Is Nothing Then
  16. Set cCell = Application.Caller
  17. End If
  18. Level = cCell.Rows.OutlineLevel
  19.  
  20. End Function