Monday, August 20, 2007

okay this is becoming more like 'one thing I learnt this week'. I will try and blog more often. Recently I learnt that labour (as in having babies) can take 36 hours... between waters breaking and contractions starting... then the painful bit starts. Apparently if men had to take the pain the human race would have died out by now.

Monday, August 13, 2007

Sorry to be an excel bore, but when I was looking for the answer to this connundrum, I saw many people asking but very few clear answers. Every person new to Excel Macros must be annoyed that when recording the 'copy down' function to copy a formula down Excel records the absolute values, not the relative values. i.e. it will record that you selected B1:B20 rather than the adjacent cells to A1:A20, which is what you wanted. Here is the VBA code to do this:

Sub SelectAdjColumn()
Dim UpBound As Range
Dim LowBound As Range

If ActiveCell.Row > 1 Then
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Set UpBound = ActiveCell.Offset(0, 0)
Else
Set UpBound = ActiveCell.End(xlUp).Offset(0, 0)
End If
Else
Set UpBound = ActiveCell.Offset(0, 0)
End If

If ActiveCell.Row < Rows.Count Then
If IsEmpty(ActiveCell.Offset(1, 0)) Then
Set LowBound = ActiveCell.Offset(0, 0)
Else
Set LowBound = ActiveCell.End(xlDown).Offset(0, 0)
End If
Else
Set LowBound = ActiveCell.Offset(0, 0)
End If

Range(UpBound.Offset(0, 1), LowBound.Offset(0, 1)).Select

Set UpBound = Nothing
Set LowBound = Nothing
End Sub

Basically what this is doing is finding the top and bottom of the used range (in the same column as the currently active cell), and then selecting the adjacent cells. By adding your copy and paste arguments before and after this it will 'copy down' the formula. I have left all the offset(0, 0) in as by changing these you can pretty much make it select any cells in the sheet you could ever want.

Monday, August 06, 2007

I use Excel extensively at work and this is something that has bugged me for a long time. Do you ever find that the scroll bar tries to scroll the entire spreadsheet instead of just the area where the data is? This is because the 'used range' has become confused somehow.

To manually reset the used range:
Select the last cell that contains data in the worksheet

To delete any unused rows:
Move down one row from the last cell with data.
Hold the Ctrl and Shift keys, and press the Down Arrow key
Right-click in the selected cells, and, from the shortcut menu, choose Delete
Select Entire Row, click OK.

To delete any unused columns:
Move right one column from the last cell with data.
Hold the Ctrl and Shift keys, and press the Right Arrow key
Right-click in the selected cells, and, from the shortcut menu, choose Delete Select Entire Column, click OK.

Save the file. Note: In older versions of Excel, you may have to Save, then close and re-open the file before the used range is reset.

Thanks to http://www.contextures.com/xlfaqApp.html#Unused

Wednesday, August 01, 2007

The word 'hampton' as in a place name orignally meant 'a farm near a bend in a river'.

Monday, July 30, 2007

So what did I learn today? Random as it may be I learnt that the fake snow in the film 'The Wizard of Oz' was actually made of Asbestos. Gosh toto!

I actually set up this blog about a year ago, but then deleted it all to use it as a platform to discuss me making a million! See www.365faces.com Well that didn't work! Seems $1 is too much to charge for anything on the web these days!

So I am now starting my old idea - One thing that I learnt today. This blog is written on the premise that we all learn new stuff everyday. Some of it we forget straight away, the other stuff is useful in some distant future. I hope to add to this pretty much everyday when I learn something new.