Archive

Archive for December, 2013

Excel macro n00b

December 10th, 2013 No comments

I just got tired and lazy while formating a report in Excel. Instead of manually clean the same rows more than 1100 times, I thought I’d better use my time creating a macro to automate the process…

All I wanted to do was to cut and paste a cell, and delete 3 rows, then repeat:

Move the cell to the right of the name (cut and paste it 2 rows above and one column right):

excel Macro 1

Delete empty rows (rows 3, 4 and 5 in the example above), and repeat this 1100 times:

excel Macro 2

So this is my extremely basic macro for doing this:

vb_code
 
  1. Sub Move_action()
  2. ' Move_action Macro
  3. ' Keyboard Shortcut: Ctrl+Shift+A
  4. Do While InStr(ActiveCell.Value, "Action") > 0
  5. ActiveCell.Cut Destination:=ActiveCell.Offset(-2, 1)
  6. ActiveSheet.Range(ActiveCell.Offset(-1, 0).EntireRow, ActiveCell.Offset(1, 0).EntireRow).Delete
  7. ActiveCell.Offset(1, 0).Select
  8. Loop
  9. End Sub

I then just selected the first “Action” cell, and ran the macro, watching Excel (slowly) do the work for me while I was eating my lunch 🙂

Categories: Tech tips Tags:
"