Wednesday, November 6, 2013

Working with Name Manager in Excel

Name Manager makes it easy to work in Excel. With the help of Name Manager you can define a range of cells by a name and store it. You can refer to the name instead of mentioning the range every time. Here is a simple VBA code to create names.

Lets say we have the following data. Column A has the Names and column B has the formula (image below)



Use the following VBA code to create the names. If the name is already available, it will overtire.

Sub CreateDefineName()
Application.DisplayAlerts = False
Sheets("Sheet1").Activate
Row = 1
Do While Cells(Row, 1) <> ""
DName = Cells(Row, 1)
Frm = Cells(Row, 2)
ActiveWorkbook.Names.Add Name:=DName, RefersTo:=Frm
Row = Row + 1
Sheets("Sheet1").Activate
Loop
Application.DisplayAlerts = True
End Sub

No comments:

Post a Comment