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
process.
Happy computing.
Damon
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))
OldRange.Name.Delete
NewRange.Name = "MyRange"
End Sub
