Excel VBA - How to copy and paste groups of data following blank cells? -

i have data in b6 , m6 correspond directly each other. data goes b6:b12 , there 2 blank cells in b13:b14. data goes on b15:b23 , there 2 blank cells , pattern repeats down page...(the same goes column m).

i researched finding blank cells , able use code grab first set of data b6:b12 , m6:m12 , paste on new worksheet in location wanted. here code:

sub copyandpaste()  nextfree = range("b6:b" & rows.count).cells.specialcells(xlcelltypeblanks).row range("b" & nextfree).select  nextfree2 = range("m6:m" & rows.count).cells.specialcells(xlcelltypeblanks).row range("m" & nextfree2).select  sheets("sheet 1").range("b6:b" & nextfree).copy destination:=sheets("sheet 2").range("b13")  sheets("sheet 1").range("m6:m" & nextfree2).copy destination:=sheets("sheet 2").range("j13")  end sub 

this works grab first group before blank 2 blank cells cannot find way grab second, third, , on groups follow 2 blank cells. appreciated.

if know pattern of blocks (block - 2 spaces - block) can nested loop.

sub grabblocks()  dim cfirst range, clast range dim sh worksheet  set sh = thisworkbook.sheets(1) set cfirst = sh.range("b6") 'first cell of each new block set clast = cfirst 'this becomes last cell of block  while not cfirst = ""      'get last cell of block     while not clast.offset(1, 0) = ""         set clast = clast.offset(1, 0)     loop      'do copy address     debug.print range(cfirst.address & ":" & clast.address).address     '... copy code goes here...      'go next block     set cfirst = clast.offset(3, 0) 'first cell of new block 2 + 1 cells below last     set clast = cfirst loop  end sub 

this code terminate when next block more 2 cells away, expecting no more blocks come.

beware these loops can become nasty if termination condition can not satisfied (e.g. cells contain 'invisible' data spaces)