The IT Hobbit - An Unexpected Journey

Nothing special just want to put a culmination of information available out there into one useful code-snippet 1: Dim wb As Workbook 2: Set wb = ActiveWorkbook 3: Dim ws As Worksheet 4: Dim lo As ListObject 5: Dim lc As ListColumn 6: Dim lr As ListRow 7: Dim frm As New frmListRanges 8: Dim txt As String 9: 10: For Each ws In wb.Worksheets 11: For Each lo In ws.ListObjects 12: txt = "" 13: For Each lr In lo.ListRows 14: txt = "{ " 15: For Each lc In lo.ListColumns 16: txt = txt & " " & CStr(lo.DataBodyRange.Cells(lr.Index, lc.Index).Value) & "," 17: Next lc 18: Trim (txt) 19: If StrComp(Right(txt, 0), ",", vbTextCompare) Then 20: txt = Left(txt, Len(txt) - 1) 21: End If 22: txt = txt & " }" 23: frm.AddNamedRange ("

Simple code to iterate through named ranges 1: Dim fm as Form 2: Dim nm As Name 3: Dim val As Double 4: 5: For Each nm In ActiveWorkbook.Names 6: 'Check MacroType and Sheet References (in Name and Value) 7: 'If (nm.MacroType < 0 And InStr(1, nm.Name, "!", vbTextCompare) = 0 And InStr(1, nm.Value, "!", vbTextCompare) = 0) Then 8: 'If (nm.MacroType < 0 And InStr(1, nm.Name, "!", vbTextCompare) = 0) Then 9: If (nm.MacroType > 0) Then 10: 'Is not a standard XIXLMMacroType (1, 2, 3) 11: frm.AddNamedRange (nm.Name &amp; "( " &amp; CStr(nm.Value) &amp; " )") 12: 'frm.AddNamedRange (nm.Name &amp; "( " &amp; CStr(nm.MacroType) &amp; " )") 13: End If 14: Next nm 15: 16: frm.Show