Formula/VBA code to remove an element in Array by its position

Hi,

I was looking for a simple formula which will remove specific element by its position from an array

for example INDEX(array,row_number) which will return the content in "row_number" of a specific array ,

similarly i would want a user defined formula (even by existing pre-defined formula of excel is fine but it may be very complicated i hope, so either by VBA code is also fine)

like rm.ele(array,row_number) where it should get as :

input array :

ar.1 = {1,2,3....65,66,67.. 100} -- total 100 elements (single dimension array)
ar.2 = {1,1,1....1,1,1...1} -- total 100 elements (single dimension array)
ar.3 = {a,b,c.... l,m,n....x,y,z} --total 26 elements (single dimension array)

execution as : rm.ele(ar.1,66) ; rm.ele(ar.2,66) ; rm.ele (ar.3,25), should return

ar.1 = {1,2,3....65,67..100} -- total 99 elements and removal of value - 66 which is the row_number as-well ;
ar.2 = {1,1,1....1,1..100} -- total 99 elements and removal of value - 1 which is at row_number 66 ;
ar.3 = {a,b,c...l.m.n...x,z} -- total 25 elements and removal of value - "y" which is at row_number 25;

Please make the code works for string and numbers

Thanks
Venkatesh MG

Hi,

Thanks for the link.
I tried exploring and just did a simple copy of whole code to VBA (Alt+F11) , but as described, it didn't returned "TRUE"(ie., progress completion) instead it returned "FALSE" indicating any possible error on the inputs or code.
My only loop-hole i guess is, i'm not sure what Array type it requires, and how different is an array that is defined by VBA and array defined by Name manager. All of the Array i defined till now is from Name manager.
If that is the bug location, then please indicate me how to convert Named range arrays to VBA arrays.

Also, i just got one more requirement. is it possible to get use of the array update as a intermediate or temp.change during only one function calls and still the input array is golden.

ie., Say ar.1 is {1..100}, by executing "DeleteArrayElement" i'm removing an element under one master function or loop, so i would also wanted ar.1 (temp) with removed element, and ar.1 (golden) without removed element - is there a way for this

Thanks,
Venkatesh MG

Originally Posted by VMG

input array :

ar.1 = {1,2,3....65,66,67.. 100} -- total 100 elements (single dimension array)

ar.1 = {1,2,3....65,67..100} -- total 99 elements and removal of value - 66 which is the row_number as-well ;

Please Login or Register  to view this content.