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.


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!

Posted in Excel, UDFs, VBA | Tagged , , , , | 5 Comments

The Gladesville Bridge is 50

The Gladesville Bridge in Sydney is much less well known than the steel arch bridge just up-river, but in many ways the methods used in its design and construction were more innovative, and at the time of its completion it was the longest span concrete arch bridge in the World.

The selection of photographs below are taken from a recently published article by Rob Wheen, who as a young man was a keen observer of the bridge construction.  The full article is freely available for download from: Gladesville Bridge Article, and members of The Concrete Institute of Australia may view the full issue (and previous magazines back to 2008) at Concrete in Australia.






Finally, The Happy Pontist has recently posted a series of articles about bridges in the Avignon region of France, including the well known original Avignon Bridge (Pont Saint-Bénézet), which was constructed in parallel segments, pre-dating the use of this technique in the Gladesville Bridge by some 700 years:

Posted in Arch structures, Historic Bridges, Newton | Tagged , | Leave a comment

Durability of Concrete Structures

Don’t put yourself or your concrete assets at risk.

Designing and constructing for durability is one of the most important aspects of any construction program. However, requirements for concrete durability are extremely fragmented in Australia. These conflicting requirements put designers, contractors, and asset owners at risk, as there a number of different approaches available that may or may not be appropriate for the specific structure.

In recent times the Concrete Institute of Australia Durability Committee, including some of Australia’s leading durability experts, have been looking at the inconsistent approach to durability issues and how this can be addressed. To highlight this work the Concrete Institute of Australia is presenting a National Seminar Series on the Durability of Concrete Structures with 5 of these leading concrete durability experts delivering a comprehensive series of presentations aligned with the major design issues.

This seminar is an ideal opportunity for designers, contractors, asset owners and suppliers, in all sectors and of all levels of experience, to develop a more consistent approach to durability and to discuss issues of current and future significance with a collection of Australian durability experts, rarely available in one room at the same time.

These 6 topics, presented by our panel of experts, will be discussed, with a view to developing a more consistent approach in the industry:

  • Durability Planning
  • Durability Exposure Zones
  • Design for Compliance
  • Construction for Durability
  • Durability Modelling
  • Durability Testing

Delegates not only get a chance to learn from, debate with, and discuss the issues with our experts, but will also receive two brand new documents on Durability produced by the Institute, as well as being the first to receive draft Durability documents, open for public comment from members and non-members attending the seminar.

With only a few days left to register, don’t miss out on this opportunity to listen to some of the best durability experts in the country!

Register here!

Posted in Concrete, Newton | Tagged , , | Leave a comment

Section Properties Update

The Section Properties spreadsheet provides formulas for the section properties of 35 different defined shapes, calculation of section properties from coordinates, and interactive calculation and plotting of any chosen shape with defined dimensions:

Section Properties for a Segment of a Circle

Section Properties for a Segment of a Circle

The latest version added the function to combine any number of shapes into a group, and provide section properties for the composite group, or from any composite shape with listed coordinates:


It recently came to my notice that the single shapes were sometimes not plotting correctly in the new version. This has now been fixed, and the new version may be downloaded from: Section Properties-Group.


Posted in Excel, Maths, Newton, VBA | Tagged , , , | 2 Comments

Life in the Universe?

What the Daily Mail said Brian Cox said:

We are alone in the universe: Professor Brian Cox says alien life is all but impossible and humanity is ‘unique’

  • Presenter makes bold claim during BBC documentary
  • He says the spark of life on earth billions of years ago was a fluke
  • ‘We still struggle to understand what happened. It’s incredibly unusual’ 

What Brian Cox said Brian Cox said:

Posted in Newton | Tagged , , , | Leave a comment