Inserting a "row" in an array
 

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.

Inserting a "row" in an array
 

is it a 1 dimensional array or two dimensional?

Sure Alan hasn't made provision for it?

--
Regards,
Tom Ogilvy


"Bob" wrote:

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.

Inserting a "row" in an array
 

Sorry, I should have mentioned that it's 2-dimensional.


"Tom Ogilvy" wrote:

is it a 1 dimensional array or two dimensional?

Sure Alan hasn't made provision for it?

--
Regards,
Tom Ogilvy


"Bob" wrote:

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.

Inserting a "row" in an array
 

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

--
Regards,
Tom Ogilvy

"Bob" wrote:

Sorry, I should have mentioned that it's 2-dimensional.


"Tom Ogilvy" wrote:

is it a 1 dimensional array or two dimensional?

Sure Alan hasn't made provision for it?

--
Regards,
Tom Ogilvy


"Bob" wrote:

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.

Inserting a "row" in an array
 

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Bob" wrote:

Sorry, I should have mentioned that it's 2-dimensional.


"Tom Ogilvy" wrote:

is it a 1 dimensional array or two dimensional?

Sure Alan hasn't made provision for it?

--
Regards,
Tom Ogilvy


"Bob" wrote:

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.

Inserting a "row" in an array
 

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

Bob wrote:
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

Inserting a "row" in an array
 

Alan,
Thanks a million!
Bob


"Alan Beban" wrote:

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

Bob wrote:
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