Range tips

A couple of tips on Excel ranges, coming from questions on the Eng-Tips Forum:

1. How to rotate a range:

The Transpose function (or option in the Paste-Special dialog box) will swap the rows and columns of a range, but there is no simple built-in way to rotate a range of data through 90 degrees.  The VBA code below will create a User Defined Function (UDF) that will do the job:

Function RotateRange(RRange As Variant) As Variant
Dim NumRows As Long, NumCols As Long, i As Long, j As Long, RotnA() As Variant

RRange = RRange.Value2
NumRows = UBound(RRange)
NumCols = UBound(RRange, 2)
ReDim RotnA(1 To NumCols, 1 To NumRows)
For i = 1 To NumRows
For j = 1 To NumCols
RotnA(j, i) = RRange(NumRows + 1 - i, j)
Next j
Next i
RotateRange = RotnA
End Function

Copy and paste this code into a VBA module.

To use the function, enter =RotateRange(address of range to be rotated) in the top-left cell of the destination range, then select the entire output range, and enter the function as an array function to display the whole rotated range.


Update 29 Nov 2014:  Further to the comments from Ian Huitson and Lori below, I have uploaded a spreadsheet with examples of the UDF and their suggestions:



2. How to show the address of a named range (or not):

The writer of this question was having a problem in Excel 2007 with named ranges in formulas being converted to cell addresses whenever a cell was edited, either by pressing the F2 key, or by double clicking on the cell.  This was happening in some files, but not others.

The solution to the problem was to go into Options-Advanced, scroll right to the bottom, and de-select the two boxes under “Lotus 123 Compatibility Settings for:”  Note that each sheet of a workbook can have different settings, so it may be necessary to check every sheet.

Further examination found that this option works differently in the more recent Excel versions (2010 and 2013).

For the later versions if you double click in the cell, or press F2 the formula box at the cell displays the cell address, but the formula bar still displays the range name.  If you click in the formula bar both the formula box and the formula bar display the cell address, rather than the range name, but the range name returns when you press enter.  This behaviour is actually quite useful, but it seems to be little known.

Strangely, Lotus 123 itself (at least in Rel. 9.5) does not replicate either of these behaviours.  When you click on a cell, or click on a formula bar, the range names remain as names, just like in Excel, when “Lotus 123 Compatibility” is not selected!

This entry was posted in Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

6 Responses to Range tips

  1. Ian Huitson says:

    In regards to Rotating a Range
    A7: =OFFSET($A$1,COLUMN($D$1)-COLUMN(),ROW()-ROW($A$7))
    Copy across 4 Columns and down 3 Rows


  2. Ian Huitson says:

    where d is a named formula =$A$1:$C$4


    • dougaj4 says:

      Hi Hui, I guess those are fairly simple, but I still like my UDF🙂


    • Lori says:

      Also worth noting that Hui’s formula can also be adapted to return an array using the “Index array trick” that has been circulating recently:



    • Lori says:

      …or a little more concisely:



  3. dougaj4 says:

    Thanks Hui and Lori, I have now uploaded a spreadsheet with examples of the UDF and all of your suggestions.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s