Check if array is consecutive then delete values in between vba -

i have array being set selected items listbox. need know how check see if there consecutive values in array delete values between lowest , highest values of consecutive numbers.

here example show mean:

dim sheets() long dim selected string  redim sheets(i) = 1 listbox1.listcount - 1     if listbox1.selected(i) = true         selected = listbox1.list(i)         redim preserve sheets(i)         sheets(i) = selected     end if next 

the array used solidworks api function sets print sheet range. why cant have more 2 consecutive numbers.

that being said if there easier way based on deselecting consecutive listbox items ears too.

thank you

with these values in listbox (all selected), get:

listbox  result -> array(1, 3, 5, 7, 9, 11)    1        1    3        3    4      5        5    7        7    8      9        9   11       11 

---

option explicit  public sub getminmaxofconsecutives()     dim sheets() long, long, totalitms long     dim prev boolean, nxt boolean, used long, long      used = 1     listbox1    'sheet1.listbox1         totalitms = .listcount - 1         redim sheets(1 totalitms)         = 1 totalitms - 1             if .selected(i)                 = .list(i)                 prev = iif(.selected(i - 1), - 1 <> .list(i - 1), true)                 nxt = iif(.selected(i + 1), + 1 <> .list(i + 1), true)                 if prev or nxt                     sheets(used) =                     used = used + 1                 end if             end if         next         if .selected(i) sheets(used) = .list(i) else used = used - 1         if used > 0 redim preserve sheets(1 used) else redim sheets(0)         'showarray sheets     end end sub 

---

private sub showarray(byref arr() long)     dim long     = lbound(arr) ubound(arr)         debug.print arr(i)     next end sub 

---

edit:

to double items not part of sequence make sure increase initial array size accommodate case:

listbox  result -> array(1, 1, 3, 3, 5, 5, 7, 7, 9, 9)    1    3    5    7    9 

---

public sub getminmaxofconsecutives2()     dim sheets() long, long, totalitms long     dim prev boolean, nxt boolean, used long, long      used = 1     listbox1         totalitms = .listcount - 1          redim sheets(1 totalitms * 2 + 1)    '<-- double upper bound          = 1 totalitms - 1             if .selected(i)                 = .list(i)                  prev = iif(.selected(i - 1), - 1 <> .list(i - 1), true)                 nxt = iif(.selected(i + 1), + 1 <> .list(i + 1), true)                  if prev or nxt                     if prev , nxt                         sheets(used) =                         used = used + 1                     end if                     sheets(used) =                     used = used + 1                 end if              end if         next          if .selected(i) sheets(used) = .list(i) else used = used - 1         if used > 0 redim preserve sheets(1 used) else redim sheets(0)         'showarray sheets      end end sub 

---

note:

if use listfillrange property fill in items in listbox make sure don't use entire columns, example don't use "a:a" because add 1+ m items list (even empty cells)

if microsoft decides increase grid size billion rows in new excel version, working listbox take long time

instead populate used range respective column:

listbox1.listfillrange = sheet1.usedrange.columns(1).address