Inserting a "row" in an array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Two of the functions within Alan Beban's excellent UDF collection allow you
to delete a "row" or "column" within an array (DeleteRow and DeleteColumn).
Unfortunately, I need to insert a "row" between two existing "rows" within an
array. Is that possible? If so, could someone kindly show me how to do that?

Thanks in advance for any guidance.
 
Dim v as Variant
Dim v1 as Variant
v = Range("A1:F30")
redim v1(1 to Ubound(v,1)+1,1 to ubound(v,2))
for i = 1 to Ubound(v,1)
if i < 15 then
k = i
else
k = i + 1
end if
for j = 1 to Ubound(v,2)
v1(k,j) = v(i,j)
Next j
Next k
v = v1
erase v1
 
Tom,
Thanks for your help!
It looks like your code inserts a new "row" between "rows" 14 and 15. Am I
reading it right? If so, can you tell me how to modify your code into a UDF
whereby I can pass it a "row number" (e.g., rownum)?
Thanks again,
Bob
 
Here's a quick and dirty function to insert a row between "betw1" and
"betw2" (the rows between which the new row will go), which relies on
functions from the downloaded file. Watch for wordwrap.

The function works only for Variant() arrays and arrays contained within
Variant variables, and it has no error checking.

Function InsertRow(inputArray, newRow, betw1, betw2) As Boolean
InsertRow = False
ResizeArray inputArray, UBound(inputArray) + 1
sa1 = SubArray(inputArray, LBound(inputArray, 2),
UBound(inputArray, 2), 1, betw1)
sa2 = SubArray(inputArray, LBound(inputArray, 2),
UBound(inputArray, 2), betw2, UBound(inputArray) - 1)
inputArray = ArrayReshape(MakeArray(sa1, newRow, sa2, 1),
UBound(inputArray), UBound(inputArray, 2))
InsertRow = True
End Function

Alan Beban
 
Alan,
Thanks a million!
Bob


Alan Beban said:
Here's a quick and dirty function to insert a row between "betw1" and
"betw2" (the rows between which the new row will go), which relies on
functions from the downloaded file. Watch for wordwrap.

The function works only for Variant() arrays and arrays contained within
Variant variables, and it has no error checking.

Function InsertRow(inputArray, newRow, betw1, betw2) As Boolean
InsertRow = False
ResizeArray inputArray, UBound(inputArray) + 1
sa1 = SubArray(inputArray, LBound(inputArray, 2),
UBound(inputArray, 2), 1, betw1)
sa2 = SubArray(inputArray, LBound(inputArray, 2),
UBound(inputArray, 2), betw2, UBound(inputArray) - 1)
inputArray = ArrayReshape(MakeArray(sa1, newRow, sa2, 1),
UBound(inputArray), UBound(inputArray, 2))
InsertRow = True
End Function

Alan Beban
Click to expand...
 
Back
Top