VBA/Excel/Access/Word/Excel/Range Name

Copy areas

 
Public Sub CopyAreas()
  Dim aRange As Range
  Dim Destination As Range
  
  Set Destination = Worksheets("Sheet3").Range("A1")
  For Each aRange In Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Areas
    aRange.Copy Destination:=Destination
    Set Destination = Destination.Offset(aRange.Rows.Count + 1)
  Next aRange
End Sub

Delete the range name

 
Sub DeleteRangeNames()
Dim rName As Name
    For Each rName In ActiveWorkbook.Names
        rName.Delete
    Next rName
End Sub