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.

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.

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.

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.

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.

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

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