October 24, 2007

Dynamic Ranges

I use dynamic ranges at work quite a lot. There's rarely a workbook I work on that doesn't have them already from a previous developer or from me adding some of my own. As they expand and contract to include data in cells, they are perfect for formulas, lists and charts, etc.

Assuming we have a list starting in A1, if it is vertical, we can use this frmula. (Go to Insert, Name, Define and enter it at the bottom where it says Refer to: with an appropiate name at the top)

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))

And if horizontal, we can use this.

=OFFSET(Sheet1!$A$1,,,,COUNTA(Sheet1!$1:$1))

Both formulas are more or less the same, the bottom one just has an extra comma and references the corresponding row (of Cell A1) rather than the column.

The thing is, when I have several of this formulas to enter, it takes time to enter these names manually. So I wrote some code to do it a little more quickly.

For vertical ranges
Sub AddDynamicRangeVertical()
    On Error Resume Next
    Dim sRangeName As String
    Dim n As Name

    If ActiveWorkbook Is Nothing Then Exit Sub

    sRangeName = InputBox("Enter a range name, then push OK. ", _
    "Add Vertical Dynamic Range")

    If sRangeName = "" Then Exit Sub

    sRangeName = Replace(sRangeName, " ", "_")

    ActiveWorkbook.Names.Add Name:=sRangeName, _
    RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _
    & ActiveCell.Address & ",,,COUNTA(" & ActiveSheet.Name _
    & "!" & Columns(ActiveCell.Column).Address & "))"

    For Each n In ActiveWorkbook.Names
        If n.Name = sRangeName Then Exit Sub
    Next n

    MsgBox Err.Description, , "Invalid Name"

    On Error GoTo 0
End Sub

For horizontal ranges
Sub AddDynamicRangeHorizontal()
    On Error Resume Next
    Dim sRangeName As String
    Dim n As Name

    If ActiveWorkbook Is Nothing Then Exit Sub

    sRangeName = InputBox("Enter a range name, then push OK. ", _
    "Add Horizontal Dynamic Range")

    If sRangeName = "" Then Exit Sub

    sRangeName = Replace(sRangeName, " ", "_")

    ActiveWorkbook.Names.Add Name:=sRangeName, _
    RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _
    & ActiveCell.Address & ",,,,COUNTA(" & ActiveSheet.Name _
    & "!" & Rows(ActiveCell.Row).Address & "))"

    For Each n In ActiveWorkbook.Names
        If n.Name = sRangeName Then Exit Sub
    Next n

    MsgBox Err.Description, , "Invalid Name"

    On Error GoTo 0
End Sub

Had to bunch the code a bit to fit my blog :-)

Well now we have some code, we need a way to run it. I use it often enough that I pasted the code into a module of my personal workbook and use some custom macro buttons from a builtin toolbar. It saves a lot of time.  

Posted by andrewe at 20:44

October 04, 2007

JMT Utilities for Excel 2007

Finally ready for download - JMT Excel Utilities for Excel 2007!

Note that the both the 97 - 2003 version and 2007 version are included in the same price. (If you have previously bought the 97 - 2003 version, write me an email and I will send the 2007 version to you free of charge)

You can download both versions here.

  
Posted by andrewe at 13:24