Resizing a named range to include titles

[davidb50]

Hi
I'm using Excel 2003. I have a dataset that covers range("A1:H87") and a dynamic named range for each column that excludes titles e.g. NamedRangeColumnA = range("A2:A87").

Within VBA, I need to be able to resize NamedRangeColumnA to include the title e.g. it becomes range("A1:A87"). I've come up with a solution below which works (with help from the MVPs on this site).

However, I was wondering if there is a simpler way to achieve this (perhaps using offset)?

Sub Learning_020()

    Dim MyRange As Range
    Dim intFirstRow As Integer
    Dim intFirstCol As Integer
    Dim intLastRow As Integer
    Dim MyResizedRange As Range
    
    Set MyRange = Range("NamedRangeColumnA")
    
    With MyRange
        intFirstRow = .Row
        intFirstCol = .Column
        intLastRow = intFirstRow + .Rows.Count - 1
    End With


    Set MyResizedRange = Range(Cells(intFirstRow - 1, intFirstCol), Cells(intLastRow, intFirstCol))
    
    MyResizedRange.Select
    
End Sub

 

[JoeMo]

Hi
I'm using Excel 2003. I have a dataset that covers range("A1:H87") and a dynamic named range for each column that excludes titles e.g. NamedRangeColumnA = range("A2:A87").

Within VBA, I need to be able to resize NamedRangeColumnA to include the title e.g. it becomes range("A1:A87"). I've come up with a solution below which works (with help from the MVPs on this site).

However, I was wondering if there is a simpler way to achieve this (perhaps using offset)?

Maybe:

Sub Learning_020()
    Dim MyRange As Range
    Dim MyResizedRange As Range
    
    Set MyRange = Range("NamedRangeColumnA")
    Set MyResizedRange = MyRange.Offset(-1, 0).Resize(MyRange.Rows.Count + 1)
    MyResizedRange.Select
End Sub

 

[Rick Rothstein]

Another way...

Sub Learning_020a()
    Dim MyResizedRange As Range
    Set MyResizedRange = Range("NamedRangeColumnA").Offset(-1).Resize(Range("NamedRangeColumnA").Rows.Count + 1)
    MyResizedRange.Select
End Sub

 

[davidb50]

Thank you