How do I append a 2D array to the end of a named range?

user_6108740 0
Viewed 79

Using VBA, I need to keep adding new values to a named range, "rng_SplitWords_Word". My new data is collected in VBA into a 2D array, "DmpAllArr".

Currently, when I run my macro, the named range cells get overwritten with the new values of the array. This is the code:

DmpAllArr = Application.WorksheetFunction.Transpose(DmpAllArr)
Range("rng_SplitWords_Word").Resize(UBound(DmpAllArr, 1)).Value = DmpAllArr

My goal is not to overwrite the named range values, but rather to append the array values to the existing named range. Though I have a few ideas of how to accomplish this technically, I cannot figure out how to implement them using VBA. They are as follows:

  1. Save the existing data from "rng_SplitWords_Word" into a variant; use "ReDim Preserve" to resize DmpAllArr, and append the variant to DmpAllArr. Then clear "rng_SplitWords_Word" of all data and write DmpAllArr to it.

  2. Somehow, append DmpAllArr to the end of "rng_SplitWords_Word"

Since "Append," "concatenate," and "join" don't work on 2D arrays, I don't know how to proceed.

I tried using .End(xlUp) to write the array to the row after the last of "rng_SplitWords_Word" but that didn't work.

Any suggestions?

vba excel arrays
2 Answers

Append Array

  • It is assumed that rng_SplitWords_Word is a one-column range and that DmpAllArr is a 2D one-based one-column array.
Sub AppendToNamedRange()
    
    Const RangeName As String = "rng_SplitWords_Word"
    
    Dim DmpAllArr As Variant: DmpAllArr = Array("A", "B", "C", "D")
    DmpAllArr = Application.WorksheetFunction.Transpose(DmpAllArr)
    Dim rCount As Long: rCount = UBound(DmpAllArr, 1)
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Names(RangeName).RefersToRange.Worksheet
    
    With ws.Range(RangeName)
        Dim lCell As Range: Set lCell = .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then
            Set lCell = .Cells(1)
        Else
            Set lCell = lCell.Offset(1)
        End If
        lCell.Resize(rCount).Value = DmpAllArr
        .Resize(lCell.Row - .Row + rCount).Name = RangeName
    End With
    
    MsgBox "New Range Address: " & ws.Range(RangeName) _
        .Address(0, 0), vbInformation

End Sub
user_98140690

This will append the values and resize the named range.

DmpAllArr = Application.WorksheetFunction.Transpose(DmpAllArr)
With Range("rng_SplitWords_Word")
    .Offset(.Rows.Count).Resize(UBound(DmpAllArr, 1)).Value = DmpAllArr
    .Resize(UBound(DmpAllArr, 1) + .Rows.Count).Name = "rng_SplitWords_Word"
End With
user_99127140
Related