Sub swapRows() ' David McRitchie, 2004-01-05, http://www.mvps.org/dmcritchie/swap.htm Dim xlong As Long If Selection.Areas.Count <> 2 Then MsgBox "Must have exactly two areas for swap." & Chr(10) _ & "You have " & Selection.Areas.Count & " areas." Exit Sub End If If Selection.Areas(1).Columns.Count <> Cells.Columns.Count Or _ Selection.Areas(2).Columns.Count <> Cells.Columns.Count Then MsgBox "Must select entire Rows, insufficient columns" Exit Sub End If Dim areaSwap1 As Range, areaSwap2 As Range, onepast2 As Range '--verify that Area 2 rows follow area 1 rows '--so that adjacent single column swap will work. If Selection.Areas(1)(1).Row > Selection.Areas(2)(1).Row Then Range(Selection.Areas(2).Address & "," & Selection.Areas(1).Address).Select Selection.Areas(2).Activate End If Set areaSwap1 = Selection.Areas(1) Set areaSwap2 = Selection.Areas(2) Set onepast2 = areaSwap2.Offset(areaSwap2.Rows.Count, 0).EntireRow areaSwap2.Cut areaSwap1.Resize(1).EntireRow.Insert Shift:=xlShiftDown areaSwap1.Cut onepast2.Resize(1).EntireRow.Insert Shift:=xlShiftDown Range(areaSwap1.Address & "," & areaSwap2.Address).Select xlong = ActiveSheet.UsedRange.Columns.Count 'correct lastcell End Sub
2. On the menu go to: Tools -> Macro -> Visual Basic Editor
3. A new window will be displayed. On the left hand side there will be a project panel that lists a code file for each sheet; such as Sheet1, Sheet2, Sheet3, etc. There will be an extra file named ThisWorkbook. This is the one we want (double-click it).
4. Now back on the right side of that area will be a white workspace. This is the code file ThisWorkbook. Copy the code above and paste it into that window. Now click Save. (There is a toolbar button or you can use the menu File -> Save [Book1]).
5. Close the Visual Basic editor window. You have just created your first macro manually. Now you can highlight the two rows you want to swap. On the menu select Tools -> Macro -> Macros... You will see the macro "ThisWorkbook.swapRows". (This is the name of the function you added through the VB editor). Select swapRows and click the Run button and that's it.
6. (Optional) On the menu select Tools -> Macro -> Macros... and select the swapRows macro but don't press the Run button. Instead click the Options button. A dialog will pop up and you will see a label that reads Shortcut key: CTRL+ and then a blank space. In that space you can type a key, like 'Z', and then from now on you can swap rows by hitting CTRL+Z rather than going through all of the menu steps.
Ok, I should of mentioned I'm running '07 (trial) version of excell, I tried to interprete what you said for whatever version you were speaking of into what version I was running, and thought I was doing it well but it wouldn't let me paste the code into the area so I guess I miss-interpreted. Thanks for your help though.
EDIT: Ok I figured it out and did everything, but there were a couple things I wasn't sure about, for instance saving the Macro, I had to save it as a whole file, so like the file type was a 'Excel Macro-Enabled Workbook' which I think is fine but not sure. And I made the short-Cut key Z, however when I go to use it, it gives me the error "You must have exactly two areas to swap. You have 1 areas." I'm not sure what I did wrong.