stltodaycom
asked on
How to reference a 'named range' from Excel using VBA?
I am using the following code to insert a new row:
==========================
Range("B9", "J9").Select
ActiveCell.Offset(0, 0).Select
Selection.EntireRow.Insert
Selection.EntireRow.Interi
Selection.EntireRow.Font.B
Selection.EntireRow.Font.C
Selection.EntireRow.Border
==========================
I want to call a 'named range' that I've established in Excel instead of
hardcoding the cell values like I am doing above: Range("B9", "J9").Select.
I am saying 'named range', but I think it is actually called 'Name Box' in Excel. (It's in the upper left hand corder beneath the file and edit menu.
How do I reference this in code? For example, I have a named range called "Objective 2".
How do I reference 'Objective 2' instead of hardcoding cell A and cell B like I am already doing?
I tried doing this but it doesn't work:
Range("Objective2").Select
ActiveCell.Offset(0, 0).Select
etc..
Thanks!
DK
==========================
Range("B9", "J9").Select
ActiveCell.Offset(0, 0).Select
Selection.EntireRow.Insert
Selection.EntireRow.Interi
Selection.EntireRow.Font.B
Selection.EntireRow.Font.C
Selection.EntireRow.Border
==========================
I want to call a 'named range' that I've established in Excel instead of
hardcoding the cell values like I am doing above: Range("B9", "J9").Select.
I am saying 'named range', but I think it is actually called 'Name Box' in Excel. (It's in the upper left hand corder beneath the file and edit menu.
How do I reference this in code? For example, I have a named range called "Objective 2".
How do I reference 'Objective 2' instead of hardcoding cell A and cell B like I am already doing?
I tried doing this but it doesn't work:
Range("Objective2").Select
ActiveCell.Offset(0, 0).Select
etc..
Thanks!
DK
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
irudyk
What part is not working? Do you get an error message? Are you getting unexpected results? I ask since the code you listed works for me.
ASKER
You guys are right. It is working.
I was still passing both arguments instead of one to my simple sub routine.
Private Sub CommandButton2_Click()
'Call AddNewEntry("B14", "J14")
Call AddNewEntry("Obj2")
End Sub
Sub AddNewEntry(ByRef NameRange As String)
'Range(CellStart, CellEnd).Select
Range(NameRange).Select
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Selection.EntireRow.Interi
Selection.EntireRow.Font.B
Selection.EntireRow.Font.C
Selection.EntireRow.Border
End Sub
I was still passing both arguments instead of one to my simple sub routine.
Private Sub CommandButton2_Click()
'Call AddNewEntry("B14", "J14")
Call AddNewEntry("Obj2")
End Sub
Sub AddNewEntry(ByRef NameRange As String)
'Range(CellStart, CellEnd).Select
Range(NameRange).Select
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Selection.EntireRow.Interi
Selection.EntireRow.Font.B
Selection.EntireRow.Font.C
Selection.EntireRow.Border
End Sub
Visual Basic Classic
Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.
165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
