MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Defining Range Names using Vba

Posted by Dexter Hall on August 02, 2001 10:58 AM

I have a small database in Excel which has been assigned a name. In Vba, I would like to be able to update the database name to include additional records. I don't want to insert the new records within the database but rather want to add them to the end of the database.

Any help would be appreciated

Posted by Damon Ostrander on August 03, 2001 2:34 PM

Hi Dexter,

I assume by "database" you mean a range of cells, and that you
want to change the definition of its range to a different range.
Here is a macro that will perhaps provide some help. It adds a
row to the range named "MyRange", redefining the range in the

Happy computing.


Sub AddRowToRange()
' Adds a row to the named range "MyRange" at the end of range

Dim iRows As Long
Dim iCols As Integer

Dim OldRange As Range
Dim NewRange As Range

Set OldRange = Range("MyRange")

iRows = OldRange.Rows.Count
iCols = OldRange.Columns.Count - 1

Set NewRange = Range(OldRange.Cells(1), OldRange.Cells(1).Offset(iRows, iCols))

NewRange.Name = "MyRange"

End Sub