I have just realized that my macro does not work if there is more than
two columns (where only one of the columns actually has the data I
wished to rank).

I am just learning VBA.
I started out with a another version of this macro, which used named
ranges rather than the Set funtion (or is it a method).
This macro works for up to 26 columns, but it clumsy because it
creates a bunch of named ranges.
I have pasted this so people can see how the macro is supposed to
work. I am pretty sure I can fix the other one so it works like this
one, but I still need to figure out R1C1 notation.

Sub RankData()
Selection.Name = "xxxx"
e = InputBox("How many columns should the gap be?")
y = Selection.Column
Z = Chr(y + 65)
c = Selection.Columns.Count
t = Selection.Rows.Count + Selection.Row - 1
u = Selection.Row + 1
Range("xxxx").Copy Range("xxxx").Offset(0, c + e)
Range("xxxx").Offset(0, c + 1).Name = "yyyy"
Range(ActiveCell.Offset(1, c - 1), ActiveCell.Offset(0,
1).End(xlDown)).Name = "xxxx1"
Range("xxxx1").Copy Range("xxxx1").Offset(0, c + e)
Range("xxxx1").Offset(0, c + e).Name = "yyyy1"
Range("yyyy1").Formula = "=RANK(" & Z & u & "," & Z & "$" & u & ":" &
Z & "$" & t & ",0)"
End Sub