Saving Multiple excel Ranges into a 2 dimensional array - not all at the same -


im writing excel macro, analyzes range of information (one row specifically), , if there triplicates (3 or more of same name in row), sets range red stands out. want every time program finds triplicate range, saves range (3 rows 8 columns) array. understand how save single range array, how add next range program finds (and next range, thereafter). after program runs , finds triplicate ranges, want take 2-d array , paste of found data empty sheet.

    k = 1 lastrow - 1                   '   if (k + 1 <= lastrow)                      if (functionarray(k + 1) = functionarray(k))                         count = count + 1                      elseif (count >= 3 , functionarray(k + 1) <> functionarray(k))                         startpoint = k - (count - 2)                         range(cells(startpoint, 1), cells(k + 1, 11)).select                         selection                             .font.bold = true                             .font.color = -16776961                             .borders(xledgeleft).linestyle = xlcontinuous                             .borders(xledgeright).linestyle = xlcontinuous                             .borders(xledgebottom).linestyle = xlcontinuous                             .borders(xledgetop).linestyle = xlcontinuous                         end                            count = 1                      elseif (count = 2 , functionarray(k + 1) <> functionarray(k))                         count = 1                     end if                   next k 

if in second if statement, wrote out logic find desired range. need save information find in 2-d array , continue add found ranges thereafter. thank help.

if read right, want take variable number of rectangular ranges and, ultimately, gather of values single 2-dimensional array can paste worksheet. 1 method create collection of ranges, feed them function gather them single array. following code shows 1 possible function test sub illustrate does. test -- put values ranges "a1:c2" , "b4:c6" , run test sub:

function compactify(ranges collection) variant 'assumes ranges non-empty collection 'of rectangular ranges     dim long, j long, m long, n long     dim block variant     dim r range, myrow range     each r in ranges         m = m + r.rows.count         if r.columns.count > n n = r.columns.count     next r     redim block(1 m, 1 n)     each r in ranges         each myrow in r.rows             = + 1             j = 1 myrow.columns.count                 block(i, j) = myrow.cells(1, j).value             next j         next myrow     next r     compactify = block end function  sub test()     dim myranges new collection     myranges.add range("a1:c2")     myranges.add range("b4:c6")     range("a10:c14").value = compactify(myranges) end sub 

Comments

Popular posts from this blog

twig - Using Twigbridge in a Laravel 5.1 Package -

jdbc - Not able to establish database connection in eclipse -

firemonkey - How do I make a beep sound in Android using Delphi and the API? -