Hi Howard,

Am Fri, 26 Feb 2016 17:25:57 +0100 schrieb Claus Busch:

Sub Test()
Dim varRows(8) As Variant

or if you don't want to count the columns first:

Sub Test()
Dim varRows() As Variant
Dim i As Long, myFirst As Long, myLast As Long
Dim myRng As Range

myFirst = Columns("I").Column
myLast = Columns("Q").Column
ReDim Preserve varRows(myLast - myFirst)

For i = myFirst To myLast
varRows(i - myFirst) = Cells(Rows.Count, i).End(xlUp).Row
Next
Set myRng = Range("I1:Q" & Application.Max(varRows))
'myRng.Select
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional