loops - How can I make my relative macro run on every 3rd row? -

loops - How can I make my relative macro run on every 3rd row? -

thanks having look.

all advice welcome i'm novice.

i've made long (and inefficient macro move info around.)

it works correctly moving 3 columns 3 rows several times across. problem need apply every 4th row running downwards 1000 rows.

i think looking loop.. not exclusively sure. appreciate way of refering don't need repeat agian , 1 time again slowing things down.

class="snippet-code-html lang-html prettyprint-override">sub fullmacro() 'copy info on cells.select selection.copy sheets.add after:=sheets(sheets.count) cells.select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _ :=false, transpose:=false ' deletes top row rows("1:35").select selection.delete shift:=xlup ' insertcolumns macro columns("b:b").select selection.insert shift:=xltoright columns("z:z").select selection.insert shift:=xltoright columns("ax:ax").select selection.insert shift:=xltoright selection.insert shift:=xltoright columns("ba:ba").select selection.insert shift:=xltoright selection.insert shift:=xltoright columns("be:be").select selection.insert shift:=xltoright columns("bg:bg").select selection.insert shift:=xltoright 'this moves titles single long row range("a2").select selection.cut destination:=range("b1") range("a3").select selection.cut destination:=range("c1") range("d2").select selection.cut destination:=range("e1") range("d3").select selection.cut destination:=range("f1") range("k2").select selection.cut destination:=range("l1") range("k3").select selection.cut destination:=range("m1") range("r3").select selection.cut destination:=range("t1") range("t1").select range("y2").select selection.cut destination:=range("z1") range("y3").select selection.cut destination:=range("aa1") range("ab2").select selection.cut destination:=range("ac1") range("ab3").select selection.cut destination:=range("ad1") range("aj2").select selection.cut destination:=range("ak1") range("aj3").select selection.cut destination:=range("al1") range("am2").select selection.cut destination:=range("an1") range("am3").select selection.cut destination:=range("ao1") range("ao1").select range("as2").select selection.cut destination:=range("at1") range("as3").select selection.cut destination:=range("au1") range("aw2").select selection.cut destination:=range("ax1") range("aw3").select selection.cut destination:=range("ay1") range("az2").select selection.cut destination:=range("ba1") range("az3").select selection.cut destination:=range("bb1") range("bd2").select selection.cut destination:=range("be1") range("bf2").select selection.cut destination:=range("bg1") range("bg1").select ' deletes colums don't need columns("h:j").select selection.delete shift:=xltoleft columns("l:n").select selection.delete shift:=xltoleft columns("m:m").select selection.delete shift:=xltoleft columns("n:q").select selection.delete shift:=xltoleft columns("t:x").select selection.delete shift:=xltoleft columns("z:ab").select selection.delete shift:=xltoleft columns("ac:ac").select selection.delete shift:=xltoleft ' deletes rows used have titles in them rows("2:3").select selection.delete shift:=xlup range("a1").select end sub sub mover() 'moves actual contents single row formatt range("a1").select activecell.offset(2, 0).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 4).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(0, 4).range("a1").select selection.cut destination:=activecell.offset(-2, 1).range("a1") activecell.offset(-1, 2).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 4).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(0, 2).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") end sub 'at moment have repeat downwards selecting new active cell , running again. want fix. sub looping() range("a5").select activecell.offset(2, 0).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 4).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(0, 4).range("a1").select selection.cut destination:=activecell.offset(-2, 1).range("a1") activecell.offset(-1, 2).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 3).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(1, 0).range("a1").select selection.cut destination:=activecell.offset(-2, 2).range("a1") activecell.offset(-1, 4).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1") activecell.offset(0, 2).range("a1").select selection.cut destination:=activecell.offset(-1, 1).range("a1")

i lose track of whats going on in code pretty quite sure not understand total extent of issue? know should in comment, not have sufficient rep.

i assume every 4th row due moving columns rows? if can reverse order , start bottom column , move up. sake of illustration move new sheet, can fixed:

sub columnstorow() set oldws = activesheet 'assuming want start in active selected cell, if fixed starting point should updated 'row , column index of activecell vcol = activecell.column vrow = activecell.row 'get lastly row in selected column maxlastrow = oldws.usedrange.rows.count '~~> lastly row index can not higher max row index in sheet set verylastindex = oldws.range(oldws.cells(maxlastrow + 1, vcol), oldws.cells(maxlastrow + 1, vcol)) '~~> sets range object cell indexed 1 higher max row index lastrowincolumn = verylastindex.end(xlup).row '~~> go verylastindex set newws = worksheets.add = lastrowincolumn vrow step -1 'insert 2 rows below newws.cells(i, vcol).offset(1).entirerow.insert newws.cells(i, vcol).offset(1).entirerow.insert 'move cells newly created rows j = 0 2 oldws.cells(i, vcol + j).copy destination:=newws.cells(i + j, vcol) next next end sub

if add together bit more description , perhaps screenshot of before -> after great.

loops excel-vba