Tag Archives: names

Excel Hints and Tips Interface

Listing all names and ranges in Excel #2

Usually, if I write a piece of utility code I check online at some point to see if it hasn’t already been done.  It’s always good to see other ways of doing something.  I try to always write the code myself first as a challenge.

Anyway, yesterday after posting my code about using VBA to list names in Excel I checked and actually found that Excel already has a way to do this built in:-

Click Insert > Name > Paste… and you get this handy little dialog.

Clicking on the ‘Paste List’ button on this form does exactly the same as yesterday’s code, barring making a new worksheet and adding bold and underlined headers!

I guess you do learn something new everyday!

Excel Visual Basic For Applications (VBA)

VBA: Listing all Names and their Ranges in Excel

Just a quick post of some code I knocked together which, when run will give you a nice list of all the names you have set up in the active workbook.  The code creates a new sheet and calls it “Workbook names” – just thought I’d share it as I’ve found it useful today.



Sub ListAllNames()

Dim myName As Name
Dim intCount As Integer

Application.Worksheets.Add
ActiveSheet.Name = "Workbook names"

Range("A1") = "Name"
Range("B1") = "Reference"

With Range("A1:B1")
.Font.Bold = True
.Font.Underline = True
End With

intCount = 2

For Each myName In ThisWorkbook.Names

Range("A" & intCount).Value = myName.Name
Range("B" & intCount).Value = myName

intCount = intCount + 1
Next

Range("A1:B1").EntireColumn.AutoFit

End Sub