CODEWITHSUNDEEP
vbaexcel
Big_Al
Big_Al

Reputation: 1

Re-order row cells

I have a prioritised job list with protected formulae in several columns calculated from adjacent numerical data. I do not want to tick the check boxes that allow the user to insert and delete rows so sheet size will be maintained.

What I am trying to do is have two command buttons, one for up, one for down, that will move the unprotected cells in a row up or down accordingly. (i.e. change the priority.) without deleting the data above or below. (effectively swap the data in the rows).

I'm thinking for the "Move Up" button, insert a range 2 rows above, copy data to these cells (based on the active cell or selected cell) then delete the cells of the original range moving the cells below up. Similar for "Move Down" button.

I can't seem to find what I am looking for as I don't believe I can use insertline due to the locked columns and the need to copy the formulae as well. Help appreciated, I'm a bit out of my depth.

Just thinking, perhaps the buttons could turn off the protection, copy, insertline 2 rows above or below, paste, deleteline (original), then turn back on the protection?

Upvotes: 0

Views: 71

Answers (1)

Ross Sharma
Ross Sharma

Reputation: 21

You could swap cell formulas/values using a temp variable.

Sub SwapCellFormulas(c1 as Range, c2 as Range)
    Dim temp as String: temp = c1.FormulaR1C1
    c1.FormulaR1C1 = c2.FormulaR1C1
    c2.FormulaR1C1 = temp
End Sub

Sub SwapCellValues(c1 as Range, c2 as Range)
    Dim temp: temp = c1.Value
    c1.Value = c2.Value
    c2.Value = temp
End Sub

Thanks to Jeeped for suggesting .FormulaR1C1 instead of .Formula

Upvotes: 2

Related Questions