excel 2013 vba range names count not working

Question

I am using Excel 2013 and cannot get the count nor a listing of range names. I can open the Names Manager dialog box and the names and ranges are listed. I added the names (List1,List2, and List3) to the workbook using VBA (Set rngTable = c.CurrentRegion.ListObjects.Add). I can iterate through the named ranges by using "List" & intCounter to get the ranges associated with each list. But I cannot get the number of names nor a list of names.

I tried following code in Excel 2013 and 2007 and when execution gets to the line For i = 1 To namedRanges.Count it returns zero (0). What can I do to get the number of named ranges in the workbook and list the names of the named ranges? Your help is greatly appreciated!

Private Sub Something()

Dim namedRanges As Names
Set namedRanges = ActiveSheet.Names

Dim targetSheet As Worksheet
Set targetSheet = Sheet3
targetSheet.Cells.Clear

Dim i As Integer
For i = 1 To namedRanges.Count
    targetSheet.Cells(i, 2).Value = namedRanges(i).Name
    targetSheet.Cells(i, 3).Value = namedRanges(i).RefersToRange.Address
Next

End Sub

Werrf Werrf · Accepted Answer · 2016-10-14T17:52:59

Are your named ranges scoped to the worksheet, or the workbook? If they're not limited to a single worksheet, you'll need to reference the workbook.Names collection instead.

Set namedRanges = ActiveWorkbook.Names

excel 2013 vba range names count not working

1 Answers