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)?
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)?
Code:
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