Excel Spreadsheets?

[J2thez929]

Excellent find

[Netsfan5]

Weird, I get this when I open web query...

[Amazinz]

Can you post a larger version of that picture? It's really tough to see what is going on there. Also, if you can post the version number of Excel that you are using.

[Sixxgunn]

I get a "web query returned no results" message. What did I do wrong?

[aaawall91]

Sometimes when I import larger numbers, such as pass yards the numbers turn into ???'s, does anyone know how to fix this, other than manually?

[aaawall91]

Nvm, figured it out, my column width was too small, but how can you do this:

Say in excel there are a list on players

Player A
Player B
Player C
Player D
Player E

How Can I make it so it says

Player A
Player D
Player B
Player C
Player E

without doing all the swapping out? Can I somehow just re-position one at a time instead stead of 2?

[Amazinz]

You need to use a macro: (not my code but it's open source)

Code: Select all

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

[aaawall91]

Thanks, but I tottaly don't know what to do with that , do I copy paste it into something...or...uhh?

[Amazinz]

1. Open up your Excel workbook.

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.

[aaawall91]

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.